﻿Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Diagnostics.Process
Imports Microsoft.Reporting.WinForms

Imports System.IO
Imports System.Data
Imports System.Text
Imports System.Drawing.Imaging
Imports System.Drawing.Printing
Imports System.Collections.Generic


Public Class Reporting

    Implements IDisposable

    Public m_currentPageIndex As Integer
    Public m_streams As IList(Of Stream)

    Public Function CreateStream(ByVal name As String, _
       ByVal fileNameExtension As String, _
       ByVal encoding As Encoding, ByVal mimeType As String, _
       ByVal willSeek As Boolean) As Stream
        'Dim stream As Stream = _
        '   New FileStream("C:\" + name + "." + fileNameExtension, FileMode.Create)

        Dim stream As Stream = _
            New FileStream(IO.Path.GetTempPath + "\" + name + "." + fileNameExtension, FileMode.Create)
        m_streams.Add(stream)
        Return stream



    End Function

    Public Sub Export(ByVal report As LocalReport, ByVal IsLandscape As Boolean)

        Dim PHeight, PWidth As Integer

        If IsLandscape Then
            PWidth = report.GetDefaultPageSettings.PaperSize.Height
            PHeight = report.GetDefaultPageSettings.PaperSize.Width
        Else
            PHeight = report.GetDefaultPageSettings.PaperSize.Height
            PWidth = report.GetDefaultPageSettings.PaperSize.Width
        End If

        Dim deviceInfo As String = _
          "<DeviceInfo>" + _
          "  <OutputFormat>EMF</OutputFormat>" + _
          "  <PageWidth>" & PWidth / 100 & "in</PageWidth>" + _
          "  <PageHeight>" & PHeight / 100 & "in</PageHeight>" + _
          "  <MarginTop>0.25in</MarginTop>" + _
          "  <MarginLeft>0.25in</MarginLeft>" + _
          "  <MarginRight>0.25in</MarginRight>" + _
          "  <MarginBottom>0.25in</MarginBottom>" + _
          "</DeviceInfo>"
        Dim warnings() As Warning = Nothing
        m_streams = New List(Of Stream)()
        report.Render("Image", deviceInfo, AddressOf CreateStream, warnings)

        Dim stream As Stream
        For Each stream In m_streams
            stream.Position = 0
        Next
    End Sub

    Public Sub PrintPage(ByVal sender As Object, ByVal ev As PrintPageEventArgs)

        Dim pageImage As New Metafile(m_streams(m_currentPageIndex))

        ev.Graphics.DrawImage(pageImage, ev.PageBounds)
        m_currentPageIndex += 1
        ev.HasMorePages = (m_currentPageIndex < m_streams.Count)

    End Sub

    Public Sub Print(ByVal IsLandScape As Boolean)

        Const printerName As String = "Microsoft Office Document Image Writer"

        If m_streams Is Nothing OrElse m_streams.Count = 0 Then
            Return
        End If

        Dim printDoc As New PrintDocument()

        ' printDoc.PrinterSettings..PrinterName = printerName

        If Not printDoc.PrinterSettings.IsValid Then
            Dim msg As String = String.Format( _
                "Can't find printer ""{0}"".", printerName)
            Console.WriteLine(msg)
            Return
        End If


        If IsLandScape Then
            printDoc.DefaultPageSettings.Landscape = True
            printDoc.DefaultPageSettings.PaperSize = New PaperSize("Landscape", 850, 1100)
        Else
            printDoc.DefaultPageSettings.Landscape = False

        End If
        
        RemoveHandler printDoc.PrintPage, AddressOf PrintPage
        AddHandler printDoc.PrintPage, AddressOf PrintPage
        ' printDoc.PrinterSettings.Duplex = Duplex.Horizontal
        printDoc.Print()

    End Sub

    Public Overloads Sub Dispose() Implements IDisposable.Dispose

        If Not (m_streams Is Nothing) Then
            Dim stream As Stream
            For Each stream In m_streams
                stream.Close()
            Next
            m_streams = Nothing
        End If

    End Sub

End Class

Public Class PeriodClass
    Public Period_ID As Integer
    Public Period_Name As String
End Class

Public Class User_Class
    Public Name As String
    Public Level As Integer
    Public Screens As New List(Of String)
End Class



Module GlobalSettings

    Public IsDfE As Boolean = True

    Public IsTest As Boolean = False

    Public Const VersionNumber = "1.998d"
    Public IsDev As Boolean = False


    Public ServerName As String
    Public DatabaseName As String

    Public StoreConnect As String
    Public CurrentUser As New User_Class
    'Public CurrentUserLevel As Integer
    Public CurrentVersionNumber As String
    Const MaxRows = 50
    Public LocationofxlTemplate As String
    Public LocationofWelcome As String
    Public ReportsPath As String
    Public CurrentPeriodID As Integer

    '  Dim tOpenForm As Thread
    Public CloseLoadingForm As Boolean = True
    Public HideSplash As Boolean = False

    Public Period_IDs As New List(Of PeriodClass)

    Dim SubNamex1 As String
    Dim SubSqlx1 As String
    Dim SubNamex2 As String
    Dim SubSqlx2 As String
    '   Dim LoadingThr As Thread

    Private m_Primatives As New Hashtable

    Private Enum Precedence
        None = 11
        Unary = 10      ' Not actually used.
        Power = 9
        Times = 8
        Div = 7
        IntDiv = 6
        Modulus = 5
        Plus = 4
    End Enum



    Public Sub CODAFile(ByVal Period_ID As Integer, ByVal Path As String, ByVal Region_Code As String, ByVal Provider_Type_Code As String, ByVal TheMonth As Integer, ByVal TheYear As Integer, ByVal RID As String)
        'xl stuff
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlRng As Excel.Range

        Dim SQL, RegionView, TheCodes, GroupBy As String
        Dim TheView As DataView
        Dim rec As DataRowView
        Dim r, c As Integer

        If Region_Code = "All" Then
            RegionView = ""
        Else
            RegionView = " where Region_Code = '" & Region_Code & "'"
            If RID <> "" Then
                RegionView = RegionView & " and RID = '" & RID & "' "
            End If
        End If

        xlApp = New Excel.ApplicationClass

        CCMDialog.RegionLabel.Text = "Progress"
        CCMDialog.UPINLabel.Text = "Current record..."
        CCMDialog.Refresh()

        CCMDialog.RegionBar.Minimum = 0
        CCMDialog.RegionBar.Maximum = 2


        SQL = "select " & _
" " & _
"		YPLA_Ref,  " & _
"		CODA_Code, " & _
"		Provider_Name, " & _
"		Sum(Value) as Value, " & _
"		sub_nominal_code, " & _
"		sub_nominal_name, " & _
"		Location_Code, " & _
"		Region_Name, " & _
"		[Deliverable Code] " & _
"		 " & _
"FROM " & _
" " & _
"( " & _
"select	case when a.Budget_ID <> 'YPLRA' and Provider_Type_Code = 'Schoo' then p.LA_Code " & _
"		else " & _
"		cast(p.UPIN as nvarchar(10)) " & _
"		end as YPLA_Ref, " & _
"		  " & _
"		CODA_Code, " & _
"		case when a.Budget_ID <> 'YPLRA' and Provider_Type_Code = 'Schoo' then p.LA_Name " & _
"		else " & _
"		p.Provider_Name " & _
"		end as Provider_Name, " & _
"		Value, " & _
"		SubCodes.sub_nominal_code, " & _
"		subCodes.sub_nominal_name, " & _
"		p.Location_Code, " & _
"		p.Region_Name, " & _
"		a.[Deliverable Code], " & _
"		 " & _
"		a.Budget_ID, p.Region_Code, p.RID " & _
"		 " & _
"from  " & _
" " & _
"(select c.UPIN, [Deliverable Code], budgets.Budget_ID, [Deliverable Name], Value from " & _
"dbo.CCM_Deliverables c, " & _
"(select distinct DeliverableCode, Budget_ID from dbo.CCM_Deliverable_Codes c, (select Allocation_ID, Budget_ID from allocation_lines UNION select 'SEN' as Allocation_ID, 'SEN' as Budget_ID UNION select 'TPG' as Allcoation_ID, 'TPG' as Budget_ID) a " & _
"where c.Allocation_id = a.Allocation_ID " & _
"and planningunit = 'Value') budgets " & _
"where " & _
"period_id = " & Period_ID & _
" and [Planning Unit Frequency] = 'Monthly Profile' " & _
"and Period = " & TheMonth & " and year = " & TheYear & " " & _
"and budgets.deliverableCode = c.[Deliverable Code]) a " & _
" " & _
"left outer join " & _
" " & _
"dbo.CODA_Sub_Nominal_Codes as SubCodes on (subcodes.DeliverableCode = a.[Deliverable Code]) " & _
" " & _
"left outer join " & _
" " & _
"dbo.CODA_Provider_Codes as coda on (coda.budget_ID = a.budget_ID and coda.UPIN = a.UPIN), " & _
" " & _
"(select p.UPIN, Provider_Name, RID, Region_Code + '000' as Location_Code, Region_Name, Region_Code, Provider_Type_Code, LA_Code, LA_Name from all_provider_fields p, " & _
"(select UPIN, Max(Lead) as lead " & _
"from all_provider_fields where lead in (1,2) " & _
"group by UPIN) m " & _
"where p.UPIN = m.UPIN and p.Lead = m.lead) p  " & _
" " & _
"where p.UPIN = a.UPIN and a.value <> 0 and Provider_Type_Code <> 'Acade' " & _
") Sub "

        GroupBy = "group by " & _
"	YPLA_Ref, " & _
"	CODA_Code, " & _
"	Provider_Name, " & _
"	sub_nominal_code, " & _
"	sub_nominal_name, " & _
"	Location_Code, " & _
"	Region_Name, " & _
"	[Deliverable Code] "

        If RegionView = "" Then
            TheCodes = " where Coda_Code is not null "
        Else
            TheCodes = " and Coda_Code is not null "
        End If


        TheView = LoadDataView(SQL & RegionView & TheCodes & GroupBy)

        CCMDialog.RegionBar.Value = 1
        CCMDialog.Refresh()

        'do the non missing codes
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = "With Codes"


        'do the headers
        xlWorkSheet.Cells(1, 1) = "UPIN"
        xlWorkSheet.Cells(1, 2) = "Provider ref."
        xlWorkSheet.Cells(1, 3) = "Provider name"
        xlWorkSheet.Cells(1, 4) = "Value excl. VAT"
        xlWorkSheet.Cells(1, 5) = "Sub nominal"
        xlWorkSheet.Cells(1, 6) = "Sub nominal name"
        xlWorkSheet.Cells(1, 7) = "Location Code"
        xlWorkSheet.Cells(1, 8) = "Location Name"
        xlWorkSheet.Cells(1, 9) = "Deliverable Code"

        If TheView.Count <> 0 Then

            CCMDialog.UPINBar.Minimum = 0
            CCMDialog.UPINBar.Maximum = TheView.Count
            CCMDialog.UPINBar.Value = 0

            'now do the records
            xlRng = xlWorkSheet.Range("A2:I" & TheView.Count + 1)

            Dim TheValues(TheView.Count - 1, 9) As Object

            r = -1

            For Each rec In TheView
                r = r + 1
                CCMDialog.UPINBar.PerformStep()
                CCMDialog.Refresh()

                For c = 0 To TheView.Table.Columns.Count - 1
                    TheValues(r, c) = rec.Item(c)
                Next
            Next

            xlRng.Value = TheValues

        End If


        '-------------------------'now the non code ones

        If RegionView = "" Then
            TheCodes = " where Coda_Code is null "
        Else
            TheCodes = " and Coda_Code is null "
        End If


        TheView = LoadDataView(SQL & RegionView & TheCodes & GroupBy)
        CCMDialog.RegionBar.Value = 2
        CCMDialog.Refresh()
        'do the missing codes
        xlWorkSheet = xlWorkBook.Sheets("sheet2")
        xlWorkSheet.Name = "Without Codes"


        'do the headers
        xlWorkSheet.Cells(1, 1) = "UPIN"
        xlWorkSheet.Cells(1, 2) = "Provider ref."
        xlWorkSheet.Cells(1, 3) = "Provider name"
        xlWorkSheet.Cells(1, 4) = "Value excl. VAT"
        xlWorkSheet.Cells(1, 5) = "Sub nominal"
        xlWorkSheet.Cells(1, 6) = "Sub nominal name"
        xlWorkSheet.Cells(1, 7) = "Location Code"
        xlWorkSheet.Cells(1, 8) = "Location Name"
        xlWorkSheet.Cells(1, 9) = "Deliverable Code"

        If TheView.Count <> 0 Then

            CCMDialog.UPINBar.Minimum = 0
            CCMDialog.UPINBar.Maximum = TheView.Count
            CCMDialog.UPINBar.Value = 0

            'now do the records
            xlRng = xlWorkSheet.Range("A2:I" & TheView.Count + 1)

            Dim TheValues(TheView.Count - 1, 9) As Object

            r = -1

            For Each rec In TheView
                r = r + 1
                CCMDialog.UPINBar.PerformStep()
                CCMDialog.Refresh()

                For c = 0 To TheView.Table.Columns.Count - 1
                    TheValues(r, c) = rec.Item(c)
                Next
            Next

            xlRng.Value = TheValues

        End If


        xlWorkBook.SaveAs(Path & "CODA_" & TheMonth & "-" & TheYear & "-" & Region_Code & "-" & Format(Now, "ddMMyyyyHHmmss"))

        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MessageBox.Show("The CODA output file has been created.", "CODA File Created", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Public Sub CCMFile_New(ByVal Period_ID As Integer, ByVal Path As String, ByVal Region_Code As String, ByVal Provider_Type_Code As String, ByVal UPIN As Integer)

        Dim CCMData As DataSet

        Dim PTC As String = ""
        Dim RC As String = ""

        If Provider_Type_Code = "All" Then
            PTC = ""
        Else
            PTC = Provider_Type_Code
        End If

        If Region_Code = "All" Then
            RC = ""
        Else
            RC = Region_Code
        End If

        'B File
        CCMData = LoadDataSet("exec dbo.sp_CCM_Output_File @FileType = 'B', @PeriodID = " & Period_ID & ", @ProviderType = '" & PTC & "', @TerritoryCode = '', @RegionCode = '" & RC & "', @LACode = '', @UPIN = " & UPIN)

        Dim HeaderView As DataView
        Dim DeliView As DataView

        Dim r As Integer

        HeaderView = CCMData.Tables(0).DefaultView
        DeliView = CCMData.Tables(1).DefaultView

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        xlApp = New Excel.ApplicationClass
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlRng As Excel.Range

        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkSheet = xlWorkBook.Sheets("sheet3")
        xlWorkSheet.Name = "Regional_Breakdown"

        '--Pointless Region breakdown Start
        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Number"
        xlWorkSheet.Cells(r, 5) = "Contract Type"
        xlWorkSheet.Cells(r, 6) = "Deliverable Name"
        xlWorkSheet.Cells(r, 7) = "Region"
        xlWorkSheet.Cells(r, 8) = "Value"
        xlWorkSheet.Cells(r, 9) = "Deliverable Code"
        xlWorkSheet.Cells(r, 10) = "Planning Unit"
        '--Pointless Region breakdown End


        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = "Contract_Header"

        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Type"
        xlWorkSheet.Cells(r, 5) = "Programme"
        xlWorkSheet.Cells(r, 6) = "Start Date"
        xlWorkSheet.Cells(r, 7) = "End Date"
        xlWorkSheet.Cells(r, 8) = "Template"
        xlWorkSheet.Cells(r, 9) = "Contract Advisor"
        xlWorkSheet.Cells(r, 10) = "Parent Contract"
        xlWorkSheet.Cells(r, 11) = "Unique Academy Identifier"
        xlWorkSheet.Cells(r, 12) = "Sponsor/Trust"

        'now do the records
        xlRng = xlWorkSheet.Range("A4:L" & HeaderView.Count + r)

        Dim TheValues(HeaderView.Count - 1, 11) As Object

        r = -1

        Dim Rec As DataRowView

        If HeaderView.Count <> 0 Then
            For Each Rec In HeaderView
                r = r + 1
                For c = 0 To HeaderView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValues(r, c) = Rec.Item(c)
                Next
            Next

            xlRng.Value = TheValues
        End If


        xlWorkSheet = xlWorkBook.Sheets("sheet2")
        xlWorkSheet.Name = "Deliverables"

        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Number"
        xlWorkSheet.Cells(r, 5) = "Contract Type"
        xlWorkSheet.Cells(r, 6) = "Deliverable Name"
        xlWorkSheet.Cells(r, 7) = "Period"
        xlWorkSheet.Cells(r, 8) = "Year"
        xlWorkSheet.Cells(r, 9) = "Value"
        xlWorkSheet.Cells(r, 10) = "Deliverable Code"
        xlWorkSheet.Cells(r, 11) = "Planning Unit"
        xlWorkSheet.Cells(r, 12) = "Planning Unit Frequency"

        'now do the records
        xlRng = xlWorkSheet.Range("A4:L" & DeliView.Count + r)

        Dim TheValuesDel(DeliView.Count - 1, 11) As Object

        r = -1

        If DeliView.Count <> 0 Then
            For Each Rec In DeliView
                r = r + 1

                For c = 0 To DeliView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValuesDel(r, c) = Rec.Item(c)
                Next
            Next

            xlRng.Value = TheValuesDel
        End If

        Dim FileName As String

        FileName = "YPLA "
        If Provider_Type_Code = "Acade" Then
            FileName = FileName & "AYF1112 "
        Else
            FileName = FileName & "16191112 "
        End If
        FileName = FileName & Format(Now, "ddMMyyyyHHmmss") & " "
        FileName = FileName & "B"

        If DeliView.Count <> 0 Then
            xlWorkBook.SaveAs(Path & FileName)
        End If

        xlWorkBook.Close(False)

        xlApp.Quit()

        Try
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        Catch ex As Exception

        End Try

        MessageBox.Show("The CCM output files have been created.", "CCM Files Created", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Public Sub CCMFile(ByVal Period_ID As Integer, ByVal Path As String, ByVal Region_Code As String, ByVal Provider_Type_Code As String, ByVal RID As String)
        'creates a CCM file(s) for the selected period

        'create a file(s) per Region
        Dim RegionView As DataView
        Dim RegionRec As DataRowView
        Dim PTSql As String
        Dim SUBSQL As String
        Dim AcadeSQL As String = ""

        Dim DelView As DataView
        Dim DelRec As DataRowView
        Dim HeadRec As DataRowView
        Dim RidRec As DataRowView
        Dim RidView As DataView

        Dim TheData As DataView

        Dim FileName As String

        Dim r As Integer

        'xl stuff
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlRng As Excel.Range

        xlApp = New Excel.ApplicationClass



        If Region_Code = "All" Then
            RegionView = LoadDataView("Select * from Regions")
        Else
            RegionView = LoadDataView("Select * from Regions where Region_Code = '" & Region_Code & "'")
        End If

        If Provider_Type_Code <> "All" Then
            PTSql = " and Provider_Type_Code = '" & Provider_Type_Code & "' "
        Else
            PTSql = " and Provider_Type_Code <> 'Acade' "
        End If

        If RID <> "" Then
            PTSql = PTSql & " and RID = '" & RID & "' "
        End If

        CCMDialog.UPINLabel.Text = ""
        CCMDialog.RegionLabel.Text = ""
        CCMDialog.RegionBar.Minimum = 0
        CCMDialog.RegionBar.Maximum = RegionView.Count
        CCMDialog.RegionBar.Value = 0
        CCMDialog.RegionBar.Step = 1
        CCMDialog.UPINBar.Minimum = 0
        CCMDialog.UPINBar.Step = 1

        For Each RegionRec In RegionView
            '************************************
            'A Files
            CCMDialog.RegionLabel.Text = "Creating 'A' file for " & RegionRec.Item("Region_Name").ToString
            CCMDialog.UPINLabel.Text = "Retrieving records, please wait..."
            CCMDialog.UPINBar.Value = 0

            CCMDialog.RegionBar.PerformStep()

            CCMDialog.Refresh()


            xlWorkBook = xlApp.Workbooks.Add(misValue)

            'now do this totaly useless regional breakdown header!!!
            xlWorkSheet = xlWorkBook.Sheets("sheet3")
            xlWorkSheet.Name = "Regional_Breakdown"

            r = 3
            xlWorkSheet.Cells(r, 1) = "Org Unit"
            xlWorkSheet.Cells(r, 2) = "UKPRN"
            xlWorkSheet.Cells(r, 3) = "UPIN"
            xlWorkSheet.Cells(r, 4) = "Contract Number"
            xlWorkSheet.Cells(r, 5) = "Contract Type"
            xlWorkSheet.Cells(r, 6) = "Deliverable Name"
            xlWorkSheet.Cells(r, 7) = "Region"
            xlWorkSheet.Cells(r, 8) = "Value"
            xlWorkSheet.Cells(r, 9) = "Deliverable Code"
            xlWorkSheet.Cells(r, 10) = "Planning Unit"

            xlWorkSheet = xlWorkBook.Sheets("sheet2")
            xlWorkSheet.Name = "Deliverables"

            RidView = LoadDataView("Select distinct RID from All_Provider_Fields where region_Code = '" & RegionRec.Item("Region_Code").ToString & "' and Lead = 1 and ((Contract_Number is null and Provider_Type_Code <> 'Schoo') or (Contract_Number is null and Parent_Contract_Number is not null and Provider_Type_Code = 'Schoo'))" & PTSql)

            ProgressBarForm.Show()
            ProgressBarForm.TheProgressBar.Minimum = 0
            ProgressBarForm.TheProgressBar.Maximum = RidView.Count
            ProgressBarForm.TheProgressBar.Step = 1
            ProgressBarForm.Refresh()

            If Provider_Type_Code = "Acade" Then
                'This is a temporary solution... please remember to change this when we start paying 201213
                AcadeSQL = " and c.[Deliverable Code]  in (select DeliverableCode from CCM_Deliverable_Codes where Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in ('YPLRD','YPLRE'))) "
            Else
                AcadeSQL = ""
            End If

            For Each RidRec In RidView

                SUBSQL = "Select c.[Period_ID],c.[Org Unit],c.[UKPRN],c.[UPIN],c.[Contract Number],c.[Contract Type],c.[Deliverable Name],c.[Period],c.[Year],sum(c.[Value]) as Value,c.[Deliverable Code],c.[Planning Unit],c.[Planning Unit Frequency] from CCM_Deliverables c where c.RID = " & _
                                   "'" & RidRec.Item("RID").ToString & "' and Period_ID = " & Period_ID & AcadeSQL & _
                                   " group by c.[Period_ID],c.[Org Unit],c.[UKPRN],c.[UPIN],c.[Contract Number],c.[Contract Type],c.[Deliverable Name],c.[Period],c.[Year],c.[Deliverable Code],c.[Planning Unit],c.[Planning Unit Frequency]"

                If ProgressBarForm.TheProgressBar.Value = 0 Then
                    RunNonQuery("drop table temp_" & CurrentUser.Name)
                    RunNonQuery("select * into temp_" & CurrentUser.Name & " from (" & SUBSQL & ") a")
                Else
                    RunNonQuery("insert into temp_" & CurrentUser.Name & " " & SUBSQL)
                End If

                ProgressBarForm.TheProgressBar.PerformStep()
                ProgressBarForm.Refresh()
                MainForm.Refresh()

            Next
            ProgressBarForm.Close()

            If RidView.Count <> 0 Then
                DelView = LoadDataView("select * from temp_" & CurrentUser.Name & " order by UPIN, [Deliverable Code], Year, Period")
            Else
                DelView = LoadDataView("select 1 as nothing where 1 = 2")
            End If

            CCMDialog.UPINLabel.Text = "Writing Deliverable Records..."
            CCMDialog.Refresh()

            CCMDialog.UPINBar.Maximum = DelView.Count

            'Do the headers
            r = 3
            xlWorkSheet.Cells(r, 1) = "Org Unit"
            xlWorkSheet.Cells(r, 2) = "UKPRN"
            xlWorkSheet.Cells(r, 3) = "UPIN"
            xlWorkSheet.Cells(r, 4) = "Contract Number"
            xlWorkSheet.Cells(r, 5) = "Contract Type"
            xlWorkSheet.Cells(r, 6) = "Deliverable Name"
            xlWorkSheet.Cells(r, 7) = "Period"
            xlWorkSheet.Cells(r, 8) = "Year"
            xlWorkSheet.Cells(r, 9) = "Value"
            xlWorkSheet.Cells(r, 10) = "Deliverable Code"
            xlWorkSheet.Cells(r, 11) = "Planning Unit"
            xlWorkSheet.Cells(r, 12) = "Planning Unit Frequency"

            'now do the records
            xlRng = xlWorkSheet.Range("A4:L" & DelView.Count + r)

            Dim TheValues(DelView.Count - 1, 11) As Object

            r = -1

            For Each DelRec In DelView
                r = r + 1
                CCMDialog.UPINBar.PerformStep()
                For c = 1 To DelView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValues(r, c - 1) = DelRec.Item(c)
                Next
            Next

            If DelView.Count <> 0 Then
                'only save files and do header records if there are actaully records to save
                xlRng.Value = TheValues

                xlWorkSheet = xlWorkBook.Sheets("sheet1")
                xlWorkSheet.Name = "Contract_Header"
                CCMDialog.UPINLabel.Text = "Writing Contract Header Records and Saving File..."
                CCMDialog.Refresh()

                'Do the headers
                r = 3
                xlWorkSheet.Cells(r, 1) = "Org Unit"
                xlWorkSheet.Cells(r, 2) = "UKPRN"
                xlWorkSheet.Cells(r, 3) = "UPIN"
                xlWorkSheet.Cells(r, 4) = "Contract Type"
                xlWorkSheet.Cells(r, 5) = "Programme"
                xlWorkSheet.Cells(r, 6) = "Start Date"
                xlWorkSheet.Cells(r, 7) = "End Date"
                xlWorkSheet.Cells(r, 8) = "Template"
                xlWorkSheet.Cells(r, 9) = "Contract Advisor"
                ' xlWorkSheet.Cells(r, 10) = "Contract Approver"
                xlWorkSheet.Cells(r, 10) = "Parent Contract"
                xlWorkSheet.Cells(r, 11) = "Unique Academy Identifier"
                xlWorkSheet.Cells(r, 12) = "Sponsor/Trust"

                Dim StartDate, EndDate As String


                '---SPEED START---
                RidView = LoadDataView("Select distinct RID from All_Provider_Fields where region_Code = '" & RegionRec.Item("Region_Code").ToString & "' and Lead = 1 and ((Contract_Number is null and Provider_Type_Code <> 'Schoo') or (Contract_Number is null and Parent_Contract_Number is not null and Provider_Type_Code = 'Schoo'))" & PTSql)

                ProgressBarForm.Show()
                ProgressBarForm.TheProgressBar.Minimum = 0
                ProgressBarForm.TheProgressBar.Maximum = RidView.Count
                ProgressBarForm.TheProgressBar.Step = 1
                ProgressBarForm.Refresh()

                If Provider_Type_Code = "Acade" Then
                    'This is a temporary solution... please remember to change this when we start paying 201213
                    AcadeSQL = " and c.[Deliverable Code]  in (select DeliverableCode from CCM_Deliverable_Codes where Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in ('YPLRD','YPLRE'))) "
                Else
                    AcadeSQL = ""
                End If


                If Provider_Type_Code = "Acade" Then
                    TheData = LoadDataView("select Min(Payment_Date) as Start_Date, Max(Payment_Date) as End_Date from dbo.Payment_Dates where Payment_ID in ( " & _
                                    "select distinct Payment_ID from dbo.Pattern_IDs " & _
                                    "where Period_ID = " & Period_ID & " " & _
                                    "and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in ('YPLRD','YPLRE')))")
                Else
                    TheData = LoadDataView("Select * from Periods where Period_ID = " & Period_ID)
                End If

                StartDate = DatePart(DateInterval.Day, TheData(0).Item("Start_Date")) & "/" & Month(TheData(0).Item("Start_Date")) & "/" & Year(TheData(0).Item("Start_Date"))
                EndDate = DatePart(DateInterval.Day, TheData(0).Item("End_Date")) & "/" & Month(TheData(0).Item("End_Date")) & "/" & Year(TheData(0).Item("End_Date"))

                For Each RidRec In RidView

                    SUBSQL = "Select distinct c.UPIN, p.Parent_Contract_Number, t.Template, c.[Contract Type], c.[Org Unit], c.Programme from CCM_Deliverables c, Providers p, CCM_Templates t where t.Provider_Type_Code = p.Provider_Type_Code and t.Period_ID = " & Period_ID & " and c.RID = " & _
                                       "'" & RidRec.Item("RID").ToString & "' and c.Period_ID = " & Period_ID & " and c.UPIN = p.UPIN" & AcadeSQL


                    If ProgressBarForm.TheProgressBar.Value = 0 Then
                        RunNonQuery("drop table temp_" & CurrentUser.Name)
                        RunNonQuery("select * into temp_" & CurrentUser.Name & " from (" & SUBSQL & ") a")
                    Else
                        RunNonQuery("insert into temp_" & CurrentUser.Name & " " & SUBSQL)
                    End If

                    ProgressBarForm.TheProgressBar.PerformStep()
                    ProgressBarForm.Refresh()
                    MainForm.Refresh()
                Next
                ProgressBarForm.Close()

                If RidView.Count <> 0 Then
                    TheData = LoadDataView("select * from temp_" & CurrentUser.Name & " order by UPIN")
                Else
                    TheData = LoadDataView("select 1 as nothing where 1 = 2")
                End If
                '---SPEED END---

                xlRng = xlWorkSheet.Range("A4:L" & TheData.Count + r)

                ReDim TheValues(TheData.Count - 1, 11)

                r = -1
                For Each HeadRec In TheData
                    r = r + 1
                    TheValues(r, 0) = HeadRec.Item("Org Unit")
                    TheValues(r, 1) = ""
                    TheValues(r, 2) = HeadRec.Item("UPIN")
                    TheValues(r, 3) = HeadRec.Item("Contract Type")
                    TheValues(r, 4) = HeadRec.Item("Programme")
                    TheValues(r, 5) = StartDate
                    TheValues(r, 6) = EndDate
                    TheValues(r, 7) = HeadRec.Item("Template")
                    TheValues(r, 8) = "broughtg"
                    ' TheValues(r, 9) = ""
                    TheValues(r, 9) = HeadRec.Item("Parent_Contract_Number")
                    TheValues(r, 10) = ""
                    TheValues(r, 11) = ""
                Next


                FileName = "YPLA "
                If Provider_Type_Code = "Acade" Then
                    FileName = FileName & "AYF1112 "
                Else
                    FileName = FileName & "16191112 "
                End If
                FileName = FileName & Format(Now, "ddMMyyyyHHmmss") & " "
                FileName = FileName & "A"

                If DelView.Count <> 0 Then
                    xlRng.Value = TheValues
                    xlWorkBook.SaveAs(Path & FileName)
                End If


            End If

            xlWorkBook.Close(False)


            '*******************************************
            'B Files
            CCMDialog.RegionLabel.Text = "Creating 'B' file for " & RegionRec.Item("Region_Name").ToString
            CCMDialog.UPINLabel.Text = "Retrieving records, please wait..."
            CCMDialog.UPINBar.Value = 0

            'CCMDialog.RegionBar.PerformStep()

            CCMDialog.Refresh()


            xlWorkBook = xlApp.Workbooks.Add(misValue)

            'now do this totaly useless regional breakdown header!!!
            xlWorkSheet = xlWorkBook.Sheets("sheet3")
            xlWorkSheet.Name = "Regional_Breakdown"

            r = 3
            xlWorkSheet.Cells(r, 1) = "Org Unit"
            xlWorkSheet.Cells(r, 2) = "UKPRN"
            xlWorkSheet.Cells(r, 3) = "UPIN"
            xlWorkSheet.Cells(r, 4) = "Contract Number"
            xlWorkSheet.Cells(r, 5) = "Contract Type"
            xlWorkSheet.Cells(r, 6) = "Deliverable Name"
            xlWorkSheet.Cells(r, 7) = "Region"
            xlWorkSheet.Cells(r, 8) = "Value"
            xlWorkSheet.Cells(r, 9) = "Deliverable Code"
            xlWorkSheet.Cells(r, 10) = "Planning Unit"

            xlWorkSheet = xlWorkBook.Sheets("sheet2")
            xlWorkSheet.Name = "Deliverables"

            '---SPEED START---
            RidView = LoadDataView("Select distinct RID from All_Provider_Fields where region_Code = '" & RegionRec.Item("Region_Code").ToString & "' and Lead = 1 and Contract_Number is not null" & PTSql)

            ProgressBarForm.Show()
            ProgressBarForm.TheProgressBar.Minimum = 0
            ProgressBarForm.TheProgressBar.Maximum = RidView.Count
            ProgressBarForm.TheProgressBar.Step = 1
            ProgressBarForm.Refresh()

            If Provider_Type_Code = "Acade" Then
                'This is a temporary solution... please remember to change this when we start paying 201213
                AcadeSQL = " and c.[Deliverable Code]  in (select DeliverableCode from CCM_Deliverable_Codes where Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in ('YPLRD','YPLRE'))) "
            Else
                AcadeSQL = ""
            End If


            For Each RidRec In RidView

                SUBSQL = "Select c.[Period_ID],c.[Org Unit],c.[UKPRN],c.[UPIN],c.[Contract Number],c.[Contract Type],c.[Deliverable Name],c.[Period],c.[Year],sum(c.[Value]) as Value,c.[Deliverable Code],c.[Planning Unit],c.[Planning Unit Frequency] from CCM_Deliverables c where c.RID = " & _
                                   "'" & RidRec.Item("RID").ToString & "' and Period_ID = " & Period_ID & AcadeSQL & _
                                   " group by c.[Period_ID],c.[Org Unit],c.[UKPRN],c.[UPIN],c.[Contract Number],c.[Contract Type],c.[Deliverable Name],c.[Period],c.[Year],c.[Deliverable Code],c.[Planning Unit],c.[Planning Unit Frequency]"


                If ProgressBarForm.TheProgressBar.Value = 0 Then
                    RunNonQuery("drop table temp_" & CurrentUser.Name)
                    RunNonQuery("select * into temp_" & CurrentUser.Name & " from (" & SUBSQL & ") a")
                Else
                    RunNonQuery("insert into temp_" & CurrentUser.Name & " " & SUBSQL)
                End If

                ProgressBarForm.TheProgressBar.PerformStep()
                ProgressBarForm.Refresh()
                MainForm.Refresh()
            Next
            ProgressBarForm.Close()

            If RidView.Count <> 0 Then
                DelView = LoadDataView("select * from temp_" & CurrentUser.Name & " order by UPIN, [Deliverable Code], Year, Period")
            Else
                DelView = LoadDataView("select 1 as nothing where 1 = 2")
            End If
            '---SPEED END---

            CCMDialog.UPINLabel.Text = "Writing Deliverable Records..."
            CCMDialog.Refresh()

            CCMDialog.UPINBar.Maximum = DelView.Count

            'Do the headers
            r = 3
            xlWorkSheet.Cells(r, 1) = "Org Unit"
            xlWorkSheet.Cells(r, 2) = "UKPRN"
            xlWorkSheet.Cells(r, 3) = "UPIN"
            xlWorkSheet.Cells(r, 4) = "Contract Number"
            xlWorkSheet.Cells(r, 5) = "Contract Type"
            xlWorkSheet.Cells(r, 6) = "Deliverable Name"
            xlWorkSheet.Cells(r, 7) = "Period"
            xlWorkSheet.Cells(r, 8) = "Year"
            xlWorkSheet.Cells(r, 9) = "Value"
            xlWorkSheet.Cells(r, 10) = "Deliverable Code"
            xlWorkSheet.Cells(r, 11) = "Planning Unit"
            xlWorkSheet.Cells(r, 12) = "Planning Unit Frequency"

            'now do the records
            xlRng = xlWorkSheet.Range("A4:L" & DelView.Count + r)

            ReDim TheValues(DelView.Count - 1, 11)

            r = -1

            For Each DelRec In DelView
                r = r + 1
                CCMDialog.UPINBar.PerformStep()
                For c = 1 To DelView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValues(r, c - 1) = DelRec.Item(c)
                Next
            Next

            If DelView.Count <> 0 Then
                xlRng.Value = TheValues

                xlWorkSheet = xlWorkBook.Sheets("sheet1")
                xlWorkSheet.Name = "Contract_Header"
                CCMDialog.UPINLabel.Text = "Writing Contract Header Records and Saving File..."
                CCMDialog.Refresh()

                'Do the headers
                r = 3
                xlWorkSheet.Cells(r, 1) = "Org Unit"
                xlWorkSheet.Cells(r, 2) = "UKPRN"
                xlWorkSheet.Cells(r, 3) = "UPIN"
                xlWorkSheet.Cells(r, 4) = "Contract Type"
                xlWorkSheet.Cells(r, 5) = "Programme"
                xlWorkSheet.Cells(r, 6) = "Start Date"
                xlWorkSheet.Cells(r, 7) = "End Date"
                xlWorkSheet.Cells(r, 8) = "Template"
                xlWorkSheet.Cells(r, 9) = "Contract Advisor"
                'xlWorkSheet.Cells(r, 10) = "Contract Approver"
                xlWorkSheet.Cells(r, 10) = "Parent Contract"
                xlWorkSheet.Cells(r, 11) = "Unique Academy Identifier"
                xlWorkSheet.Cells(r, 12) = "Sponsor/Trust"



                FileName = "YPLA "
                If Provider_Type_Code = "Acade" Then
                    FileName = FileName & "AYF1112 "
                Else
                    FileName = FileName & "16191112 "
                End If
                FileName = FileName & Format(Now, "ddMMyyyyHHmmss") & " "
                FileName = FileName & "B"

                If DelView.Count <> 0 Then
                    xlRng.Value = TheValues
                    xlWorkBook.SaveAs(Path & FileName)
                End If




            End If



            xlWorkBook.Close(False)
        Next


        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MessageBox.Show("The CCM output files have been created.", "CCM Files Created", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Public Function VaryPaymentPatternV2(ByVal RID As String, ByVal AllocationID As String, ByVal PeriodID As Integer, ByVal NewAllocation As Double) As Boolean
        'no need to make payments anymore, because that will be done at the point of the CCM creation file
        'always run this before you commit allocation changes to the DB

        Dim SQL As String
        Dim TheData As DataView

        'Is there a pattern national or provider level...?
        SQL = "select coalesce(sum(Percentage),0) as Percentage from dbo.Patterns_Active where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "'"
        TheData = LoadDataView(SQL)

        If TheData(0).Item("Percentage") = 0 Then
            'There is no pattern, so just exit... nothing to change
            Return True
            Exit Function
        End If

        'get the Total Paid and Maximum Month so far
        Dim TotalPaid As Double
        Dim MaxPeriod As Date
        Dim Remainder As Double

        SQL = "select coalesce(sum(Month_Total),0) as Total, max(month) as Month from payments where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "'"
        TheData = LoadDataView(SQL)

        TotalPaid = TheData(0).Item("Total")

        If IsDBNull(TheData(0).Item("Month")) Then
            SQL = "Select Start_date from Periods where Period_ID = " & PeriodID
            TheData = LoadDataView(SQL)

            MaxPeriod = DateSerial(Year(TheData(0).Item("Start_Date")), Month(TheData(0).Item("Start_Date")) - 1, 1)
        Else
            MaxPeriod = TheData(0).Item("Month")
        End If
        Remainder = NewAllocation - TotalPaid

        If NewAllocation = 0 Then
            'have payments been made?
            If TotalPaid = 0 Then
                'nothing paid no worries.. just exit
                Return True
            Else
                'The new allocation is 0 and some payments have already been made so oops! Warn the user, make a negative payment.
                Dim Payment As Double
                Payment = TotalPaid * -1

                If MessageBox.Show("Some payments have already been made, so a negative payment of £" & FormatNumber(TotalPaid, 2, TriState.True, TriState.UseDefault, TriState.True) & _
                                " will be made.  Are you sure you want to continue with this?", "Negative payment will have to be made", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.Yes Then

                    'make the negative payment
                    Dim NextPeriod As Date

                    If Month(MaxPeriod) = 12 Then
                        NextPeriod = DateSerial(Year(MaxPeriod) + 1, 1, 1)
                    Else
                        NextPeriod = DateSerial(Year(MaxPeriod), Month(MaxPeriod) + 1, 1)
                    End If

                    SQL = "insert into Payments (Period_ID, RID, Allocation_ID, Payment_Date, Month, Month_Total) " & _
                          "Values (" & PeriodID & ", '" & RID & "', '" & AllocationID & "', " & Now.ToOADate - 2 & ", " & NextPeriod.ToOADate - 2 & ", " & Payment & ")"
                    RunNonQuery(SQL)
                    Return True
                Else
                    Return False
                End If
            End If
            Exit Function
        End If

        'get the Total Remaining pattern
        Dim TotalPattern As Double

        SQL = "select coalesce(sum(Percentage),0) as Percentage from dbo.Patterns_Active where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "' and Month > " & MaxPeriod.ToOADate - 2
        TheData = LoadDataView(SQL)

        TotalPattern = TheData(0).Item("Percentage")


        'Load the Payment List with the Payments
        Dim PaymentList As New List(Of Double)
        Dim PeriodList As New List(Of Date)
        Dim Rec As DataRowView

        SQL = "select Month, coalesce(sum(Month_Total),0) as Month_Total from payments where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "' group by Month"
        TheData = LoadDataView(SQL)
        PaymentList.Clear()
        PeriodList.Clear()

        For Each Rec In TheData
            PaymentList.Add(Rec.Item("Month_Total"))
            PeriodList.Add(Rec.Item("Month"))
        Next

        'now load the rest of the year with the potential payments into the list
        SQL = "select Month, Percentage from dbo.Patterns_Active where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "' and Month > " & MaxPeriod.ToOADate - 2
        TheData = LoadDataView(SQL)

        Dim NewValue As Double

        If TotalPattern <> 0 Then
            For Each Rec In TheData
                NewValue = (Rec.Item("Percentage") / TotalPattern) * Remainder
                PaymentList.Add(NewValue)
                PeriodList.Add(Rec.Item("Month"))
            Next
        End If

        'now create/edit the provider pattern
        SQL = "delete from provider_patterns where RID = '" & RID & "' and Period_ID = " & PeriodID & " and Allocation_ID = '" & AllocationID & "'"
        RunNonQuery(SQL)

        Dim x As Integer = 0
        Dim Perc As Double

        For x = 0 To PaymentList.Count - 1
            Perc = PaymentList(x) / NewAllocation

            SQL = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) " & _
                  "Values (" & PeriodID & ", '" & AllocationID & "', '" & RID & "', " & PeriodList(x).ToOADate - 2 & ", " & Perc & ")"

            RunNonQuery(SQL)
        Next

        If TotalPattern = 0 Then
            MessageBox.Show("Please note that it was not possible to adjust the percentage pattern to pay the new ammount fully, please go and change the pattern manually", "Manual adjustement needed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If
        Return True
    End Function

    Public Sub VaryPaymentPattern(ByVal RID As String, ByVal AllocationID As String, ByVal PeriodID As Integer, ByVal NewAllocation As Double)

        'always run this before you commit allocation changes to the DB

        Dim sql As String
        Dim TheView As DataView
        Dim AView As DataView
        Dim QView As DataView
        Dim TheDate As Double
        Dim RecDate As Date
        Dim NewPercent As Double

        Dim r As DataRowView

        TheDate = Now.ToOADate - 2

        'first make the payments on the original amounts and original pattern
        'insert but dont overwrite, because use what has already been paid anyway and just add the extra months if any.

        sql = "select * from PaymentProfiles where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString & " and Month <= " & TheDate.ToString

        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            'This is a brand new provider or allocation never existed before.
            Exit Sub
        End If

        For Each r In TheView

            RecDate = r.Item("Month")

            'dont add it if it has already been paid
            sql = "select * from payments where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString & " and Month = " & RecDate.ToOADate - 2

            QView = LoadDataView(sql)
            If QView.Count = 0 Then
                sql = "insert into Payments (Period_ID, RID, Allocation_ID, Payment_Date, Month, Month_Total) values " & _
                  "(" & PeriodID.ToString & ",'" & RID & "','" & AllocationID & "', " & TheDate & ", " & RecDate.ToOADate - 2 & ", " & r.Item("Month_Total").ToString & ")"

                RunNonQuery(sql)
            End If

        Next



        'Now that the payments have been made on the original pattern change the pattern to fit the new amount

        'get the old pattern into The View
        'But this is not as easy as it sounds.  You need to go and get a pattern based on the entire budget, not just this line
        Dim Budget_IDs As String

        If AllocationID.Contains("YPC") Then
            'its a school
            Budget_IDs = "('YPLRC')"
        Else
            If AllocationID.Contains("YPN") Then
                Budget_IDs = "('YPLRN')"
            Else
                Budget_IDs = "('YPLRA', 'YPLRB')"
            End If

        End If

        'get the total allocation

        TheView = Nothing

        sql = "select sum(Coalesce(Allocation,0)) as Total from Allocations where RID = '" & RID & "' and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in " & Budget_IDs & ") and Status = 'Approved' and Period_ID = " & PeriodID.ToString
        TheView = LoadDataView(sql)

        Dim OldTotalAllocation As Double

        If IsDBNull(TheView(0).Item("Total")) Then
            OldTotalAllocation = 0
        Else
            OldTotalAllocation = TheView(0).Item("Total")
        End If

        'now get them ind.
        sql = "select Month, sum(coalesce(Month_Total,0)) as Total from PaymentProfiles where RID = '" & RID & "' and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in " & Budget_IDs & ") and Period_ID = " & PeriodID.ToString & " group by Month"
        TheView = LoadDataView(sql)

        sql = ""
        For Each r In TheView
            Dim TheMonth As DateTime
            TheMonth = r.Item("Month")

            Dim ThePercentage
            If OldTotalAllocation = 0 Then
                ThePercentage = 0
            Else
                ThePercentage = r.Item("Total") / OldTotalAllocation
            End If

            If sql = "" Then
                sql = "Select " & TheMonth.ToOADate - 2 & " as Month, " & ThePercentage & " as Percentage "
            Else
                sql = sql & "Union All Select " & TheMonth.ToOADate - 2 & " as Month, " & ThePercentage & " as Percentage "
            End If
        Next

        TheView = LoadDataView(sql)

        'The Pattern is in The view now calculate the new pattern based on the new allocation minus what has already been paid.

        'get what has been paid
        Dim TotalPayments As Double


        sql = "select coalesce(sum(Month_Total),0) as Total from PaymentProfiles where RID = '" & RID & "' and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in " & Budget_IDs & ") and Period_ID = " & PeriodID.ToString & " and Month <= " & TheDate.ToString
        AView = LoadDataView(sql)

        TotalPayments = AView(0).Item("Total")


        'Positive profile (make positive payments)

        'now delete the current provider pattern
        'lets not delete the old pattern... because this makes it very confusing
        'sql = "delete from Provider_Patterns where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString
        'RunNonQuery(sql)

        'quickly run through the pattern to get the total % after payments
        Dim TotalPercent As Double
        TotalPercent = 0

        Dim IntDate As Integer

        If TheView Is Nothing Then
            'this is a brand new provider.  Its best we exit this
            Exit Sub
        End If

        For Each r In TheView

            IntDate = r.Item("Month")

            If IntDate <= TheDate Then
            Else
                TotalPercent = TotalPercent + r.Item("Percentage")

            End If
        Next

        Dim TheValue As Double

        For Each r In TheView

            IntDate = r.Item("Month")

            If IntDate <= TheDate Then
                'lets not do this anymore
                'Lets insert the payment made against the new allocation to set the % for the new pattern
                '      sql = "select * from Payments where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString & " and Month = " & IntDate
                '      AView = LoadDataView(sql)
                '
                '               If AView.Count = 0 Then
                ' TheValue = 0
                'Else
                'TheValue = AView(0).Item("Month_Total")
                ' End If

                '    If NewAllocation <> 0 Then
                'Only change the pattern if the new allocation is not 0
                'NewPercent = TheValue / NewAllocation
                ' sql = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) values (" & PeriodID.ToString & ", '" & AllocationID & "', '" & RID & "', " & IntDate & ", " & NewPercent.ToString & ")"
                ' RunNonQuery(sql)
                '     End If

            Else

                'insert the new pattern... actually no!  Insert the payment instead... leave the pattern be
                'get the old individual allocation
                sql = "select coalesce(Allocation,0) as Total from Allocations where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Status = 'Approved' and Period_ID = " & PeriodID.ToString
                AView = LoadDataView(sql)

                Dim OriginalIndAllocation As Double

                If AView.Count = 0 Then
                    OriginalIndAllocation = 0
                Else
                    OriginalIndAllocation = AView(0).Item("Total")
                End If

                NewPercent = r.Item("Percentage") / TotalPercent
                TheValue = Math.Round(((OldTotalAllocation + (NewAllocation - OriginalIndAllocation)) - TotalPayments) * NewPercent)

                'get every months old total payment by month
                sql = "select sum(Coalesce(Month_Total,0)) as Total from PaymentProfiles where RID = '" & RID & "' and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID in " & Budget_IDs & ") and Period_ID = " & PeriodID.ToString & " and Month = " & IntDate
                AView = LoadDataView(sql)

                '  If NewAllocation <> 0 Then
                'Only change the pattern if the new allocation is not 0

                'NewPercent = (TheValue - AView(0).Item("Total")) / NewAllocation

                'sql = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) values (" & PeriodID.ToString & ", '" & AllocationID & "', '" & RID & "', " & IntDate & ", " & NewPercent.ToString & ")"
                'RunNonQuery(sql)
                'Else
                'lets make the payment if the new allocation is 0 (because no percentage based on a 0 allocaiton will ever make a negative payment)

                Dim AValue As Double
                AValue = (TheValue - AView(0).Item("Total"))

                'get individual payment
                sql = "select Coalesce(Month_Total,0) as Total from PaymentProfiles where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString & " and Month = " & IntDate
                AView = LoadDataView(sql)

                If AView.Count <> 0 Then
                    AValue = AValue + AView(0).Item("Total")
                End If

                'Now delete old payment

                sql = "delete from Payments where RID = '" & RID & "' and Allocation_ID = '" & AllocationID & "' and Period_ID = " & PeriodID.ToString & " and Month = " & IntDate
                RunNonQuery(sql)


                sql = "insert into Payments (Period_ID, RID, Allocation_ID, Payment_Date, Month, Month_Total) values " & _
              "(" & PeriodID.ToString & ",'" & RID & "','" & AllocationID & "', " & TheDate & ", " & IntDate & ", " & AValue & ")"

                RunNonQuery(sql)
                'End If


            End If


        Next

    End Sub


    Public Function RemoveBadChars(ByVal TheString As String) As String

        TheString = TheString.Replace("'", "")
        '  TheString = TheString.Replace("&", "and")

        RemoveBadChars = TheString

    End Function

    Public Sub MergeColumns(ByVal TheGrid As System.Windows.Forms.DataGridView, ByVal FromColumn As Integer, ByVal ToColumn As Integer, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs)
        If e.ColumnIndex >= FromColumn AndAlso e.RowIndex <> -1 AndAlso e.ColumnIndex <= ToColumn Then

            Using gridBrush As Brush = New SolidBrush(TheGrid.GridColor), backColorBrush As Brush = New SolidBrush(e.CellStyle.BackColor), selectColorBrush As Brush = New SolidBrush(e.CellStyle.SelectionBackColor)

                Using gridLinePen As Pen = New Pen(gridBrush)
                    ' Clear cell

                    Try
                        'fill it with back or selection
                        If TheGrid.CurrentCell.ColumnIndex = e.ColumnIndex And TheGrid.CurrentCell.RowIndex = e.RowIndex Then
                            e.Graphics.FillRectangle(selectColorBrush, e.CellBounds)
                        Else
                            e.Graphics.FillRectangle(backColorBrush, e.CellBounds)
                        End If
                    Catch ex As Exception
                        e.Graphics.FillRectangle(backColorBrush, e.CellBounds)
                    End Try



                    ' Draw line (bottom border and right border of current cell)  
                    'If next row cell has different content, only draw bottom border line of current cell  
                    If e.RowIndex < TheGrid.Rows.Count - 1 AndAlso TheGrid.Rows(e.RowIndex + 1).Cells(e.ColumnIndex).Value.ToString() <> e.Value.ToString() Then
                        e.Graphics.DrawLine(gridLinePen, e.CellBounds.Left, e.CellBounds.Bottom - 1, e.CellBounds.Right - 1, e.CellBounds.Bottom - 1)
                    End If

                    ' Draw right border line of current cell  
                    e.Graphics.DrawLine(gridLinePen, e.CellBounds.Right - 1, e.CellBounds.Top, e.CellBounds.Right - 1, e.CellBounds.Bottom)

                    ' draw/fill content in current cell, and fill only one cell of multiple same cells  
                    If Not e.Value Is Nothing Then
                        If e.RowIndex > 0 AndAlso TheGrid.Rows(e.RowIndex - 1).Cells(e.ColumnIndex).Value.ToString() = e.Value.ToString() Then
                        Else
                            Try
                                If TheGrid.CurrentCell.ColumnIndex = e.ColumnIndex And TheGrid.CurrentCell.RowIndex = e.RowIndex Then
                                    e.Graphics.DrawString(CType(e.Value, String), e.CellStyle.Font, Brushes.White, e.CellBounds.X + 2, e.CellBounds.Y + 5, StringFormat.GenericDefault)
                                Else
                                    e.Graphics.DrawString(CType(e.Value, String), e.CellStyle.Font, Brushes.Black, e.CellBounds.X + 2, e.CellBounds.Y + 5, StringFormat.GenericDefault)
                                End If
                            Catch ex As Exception
                                e.Graphics.DrawString(CType(e.Value, String), e.CellStyle.Font, Brushes.Black, e.CellBounds.X + 2, e.CellBounds.Y + 5, StringFormat.GenericDefault)
                            End Try


                        End If
                    End If

                    e.Handled = True
                End Using
            End Using
        End If
    End Sub

    Public Function GetRegionCode(ByVal LA_Code As String) As String
        Dim sql As String
        Dim TheView As DataView

        sql = "select * from Local_Authorities where LA_Code = '" & LA_Code & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            GetRegionCode = ""
        Else
            GetRegionCode = TheView(0).Item("Region_Code")
        End If

    End Function

    Public Function CloseStore() As String
        Dim TheView As DataView
        Dim sql As String

        sql = "Select * from Close_Store"

        TheView = LoadDataView(sql)

        If TheView.Count <> 0 Then
            If TheView(0).Item("Close_Store") = 1 Then
                CloseStore = TheView(0).Item("Message").ToString
            Else
                CloseStore = ""
            End If
        Else
            CloseStore = ""
        End If

        TheView = Nothing

    End Function

    Public Function StringToCurrency(ByVal TheString As String) As String
        'remove any commas and pound signs

        Dim a As String

        a = TheString

        a = a.Replace("£", "")
        a = a.Replace(",", "")

        Try
            a = Decimal.Parse(a).ToString("c")
        Catch ex As Exception

        End Try

        StringToCurrency = a

    End Function


    Public Sub ShowReport(ByVal ReportTitle As String, ByVal ReportName As String, _
                          ByVal Dataset1 As String, ByVal sql1 As String, ByVal Dataset2 As String, _
                          ByVal sql2 As String, ByVal Dataset3 As String, ByVal sql3 As String, _
                          ByVal Parameter1 As String, ByVal Parameter1Val As String, _
                          ByVal Parameter2 As String, ByVal Parameter2Val As String, _
                          ByVal Parameter3 As String, ByVal Parameter3Val As String, _
                          ByVal Parameter4 As String, ByVal Parameter4Val As String, _
                          ByVal Parameter5 As String, ByVal Parameter5Val As String, _
                          ByVal Parameter6 As String, ByVal Parameter6Val As String, _
                          ByVal Parameter7 As String, ByVal Parameter7Val As String, _
                          ByVal Parameter8 As String, ByVal Parameter8Val As String, _
                          ByVal SubName1 As String, ByVal SubSql1 As String, _
                          ByVal SubName2 As String, ByVal subSql2 As String, ByVal PrintIt As Boolean, ByVal PrintLandScape As Boolean, Optional ByVal Dataset4 As String = "", Optional ByVal sql4 As String = "")

        Dim pInfo As ReportParameterInfoCollection
        Dim paramList As New Generic.List(Of ReportParameter)
        Dim TheDataView As DataView
        Dim DataSetName As String



        SubNamex1 = SubName1
        SubSqlx1 = SubSql1
        SubNamex2 = SubName2
        SubSqlx2 = subSql2

        With ReportViewerForm

            .Text = ReportTitle
            .MdiParent = MainForm
            .Dock = DockStyle.Fill
            .Show()
            .ReportViewer.Reset()

            RemoveHandler .ReportViewer.LocalReport.SubreportProcessing, _
                                       AddressOf .SubreportProcessingEvent
            AddHandler .ReportViewer.LocalReport.SubreportProcessing, _
                                       AddressOf .SubreportProcessingEvent

            .ReportViewer.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local
            .ReportViewer.LocalReport.EnableExternalImages = True
            .ReportViewer.LocalReport.EnableHyperlinks = True
            .ReportViewer.LocalReport.ReportPath = ReportsPath & ReportName
            ' .ReportViewer.LocalReport.ExecuteReportInCurrentAppDomain(System.Reflection.Assembly.GetExecutingAssembly().Evidence)

            .ReportViewer.Refresh()


            If Parameter1 <> "" Then
                paramList.Add(New ReportParameter(Parameter1, Parameter1Val, False))
            End If

            If Parameter2 <> "" Then
                paramList.Add(New ReportParameter(Parameter2, Parameter2Val, False))
            End If

            If Parameter3 <> "" Then
                paramList.Add(New ReportParameter(Parameter3, Parameter3Val, False))
            End If

            If Parameter4 <> "" Then
                paramList.Add(New ReportParameter(Parameter4, Parameter4Val, False))
            End If

            If Parameter5 <> "" Then
                paramList.Add(New ReportParameter(Parameter5, Parameter5Val, False))
            End If

            If Parameter6 <> "" Then
                paramList.Add(New ReportParameter(Parameter6, Parameter6Val, False))
            End If

            If Parameter7 <> "" Then
                paramList.Add(New ReportParameter(Parameter7, Parameter7Val, False))
            End If

            If Parameter8 <> "" Then
                paramList.Add(New ReportParameter(Parameter8, Parameter8Val, False))
            End If

            If paramList.Count <> 0 Then
                .ReportViewer.LocalReport.SetParameters(paramList)

            End If

            'pInfo = .ReportViewer.LocalReport.GetParameters()

            .ReportViewer.LocalReport.DataSources.Clear()

            .Cursor = Cursors.WaitCursor
            TheDataView = LoadDataView(sql1)
            .Cursor = Cursors.Default
            DataSetName = Dataset1

            .ReportViewer.LocalReport.DataSources.Add(New ReportDataSource(DataSetName, TheDataView))

            If Dataset2 <> "" Then
                .Cursor = Cursors.WaitCursor
                TheDataView = LoadDataView(sql2)
                .Cursor = Cursors.Default
                DataSetName = Dataset2

                .ReportViewer.LocalReport.DataSources.Add(New ReportDataSource(DataSetName, TheDataView))
            End If

            If Dataset3 <> "" Then
                .Cursor = Cursors.WaitCursor
                TheDataView = LoadDataView(sql3)
                .Cursor = Cursors.Default
                DataSetName = Dataset3

                .ReportViewer.LocalReport.DataSources.Add(New ReportDataSource(DataSetName, TheDataView))
            End If

            If Dataset4 <> "" Then
                .Cursor = Cursors.WaitCursor
                TheDataView = LoadDataView(sql4)
                .Cursor = Cursors.Default
                DataSetName = Dataset4

                .ReportViewer.LocalReport.DataSources.Add(New ReportDataSource(DataSetName, TheDataView))
            End If

            If PrintIt = False Then
                .ReportViewer.RefreshReport()
            Else
                Dim myPRT = New Reporting

                myPRT.Export(.ReportViewer.LocalReport, PrintLandScape)
                myPRT.m_currentPageIndex = 0
                myPRT.Print(PrintLandScape)
            End If

        End With
    End Sub



    Public Function SubreportDataView(ByVal SubreportName As String) As DataView

        SubreportDataView = Nothing

        If SubNamex1 <> "" Then

            If SubreportName = SubNamex1 Then
                SubreportDataView = LoadDataView(SubSqlx1)
            End If
        Else
            SubreportDataView = Nothing

        End If

        If SubNamex2 <> "" Then
            If SubreportName = SubNamex2 Then
                SubreportDataView = LoadDataView(SubSqlx2)
            End If
        Else
            SubreportDataView = Nothing
        End If

    End Function

    Public Sub Job(ByVal TheText As String)
        MainForm.JobLabel.Text = TheText
        MainForm.Refresh()
    End Sub

    Public Sub LogonUser()
        Dim TheData As DataView
        Dim Sql As String
        Dim TheDate As Double

        'just for why not
        Period_IDs.Clear()

        Sql = "Select * from Users where User_Name = '" & CurrentUser.Name & "'"

        TheData = LoadDataView(Sql)

        TheDate = Now.ToOADate - 2

        If TheData.Count <> 0 Then
            'The user already exists so just update the logon date and load the user level
            RunNonQuery("Update Users set Last_Logon = " & TheDate & " where User_Name = '" & CurrentUser.Name & "'")
            CurrentUser.Level = TheData(0).Item("User_Level")
        Else
            If Not IsDfE Then
                'bofore adding the user make sure he/she is an YPLA user
                Dim currentADUser As System.DirectoryServices.AccountManagement.UserPrincipal
                currentADUser = System.DirectoryServices.AccountManagement.UserPrincipal.Current

                If Not currentADUser.EmailAddress.Contains("@ypla.gov.uk") Then
                    HideSplashScreen()
                    MainForm.LogonLabel.Text = "No access"

                    Windows.Forms.MessageBox.Show("You are not a YPLA user.  If you require access, please send an Email to TheStore.EFA@education.gsi.gov.uk", "No access", MessageBoxButtons.OK, MessageBoxIcon.Error)

                    Application.Exit()
                    Exit Sub
                End If
            End If

            RunNonQuery("Insert into Users values('" & CurrentUser.Name & "', 0, " & TheDate & ", null, null)")
            CurrentUser.Level = 0
        End If

        User_Access()

        AddActivity(CurrentUser.Name, "User logged on", 1, "")

        TheData = Nothing

        'MainForm.CheckCloseStore.Enabled = True
    End Sub

    Public Sub AddActivity(ByVal User_Name As String, ByVal Activity As String, ByVal TheType As Integer, ByVal Extra As String)

        Dim Sql As String
        Dim TheDate As Double

        TheDate = Now.ToOADate - 2

        'Update activities table
        Sql = "Insert into User_Activities values('" & CurrentUser.Name & "'," & TheDate & ", '" & Activity & "', " & TheType & ", '" & Extra & "')"

        RunNonQuery(Sql)

    End Sub

    Public Sub LoadGlobalSettingsFromDB()
        Dim TheData As DataView
        Dim Sql As String

        Sql = "Select * from Global_Settings_Upgrade"

        TheData = LoadDataView(Sql)

        LocationofxlTemplate = TheData(0).Item("Volumes_Template_Path").ToString
        LocationofWelcome = TheData(0).Item("Home_Path").ToString
        ReportsPath = TheData(0).Item("Reports_Path").ToString
        CurrentPeriodID = TheData(0).Item("Current_Period_ID")

        'do a version check

        'check to see if this is a development environment
        CurrentVersionNumber = TheData(0).Item("Version_Number").ToString
        If TheData(0).Item("Version_Number").ToString = "DEV" Then
            MainForm.Text = "The Store - Development Environment"

        Else

            If VersionNumber <> TheData(0).Item("Version_Number").ToString Then
                HideSplashScreen()
                TheData = Nothing
                MessageBox.Show(MainForm, "You are not using the correct version.  The application will now quit", _
                                "Wrong version number", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Application.Exit()

            End If
        End If
        TheData = Nothing

    End Sub

    Public Function HasAccess(ByVal frm As Form) As Boolean

        Dim Answer As Boolean = False

        If CurrentUser.Screens.Count = 0 Then
            Answer = False
        Else
            Dim r As Integer

            For r = 0 To CurrentUser.Screens.Count - 1
                If CurrentUser.Screens(r) = frm.Name Then
                    Answer = True
                End If
            Next
        End If

        Return Answer
    End Function

    Public Function IsFormOpen(ByVal frm As Form) As Boolean
        If Application.OpenForms.OfType(Of Form).Contains(frm) Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Sub ShowSplashScreen()
        If Not IsFormOpen(SplashScreen) Then
            SplashScreen.Show()
            SplashScreen.Refresh()

        End If
    End Sub

    Public Sub HideSplashScreen()
        If IsFormOpen(SplashScreen) Then
            SplashScreen.Close()
        End If

        Job("Ready")


    End Sub

    Public Sub ShowLoadingForm()
        If Not IsFormOpen(LoadingForm) Then
            LoadingForm.Show()
            LoadingForm.Refresh()

        End If


    End Sub



    Public Sub HideLoadingForm()

        If IsFormOpen(LoadingForm) Then
            LoadingForm.Close()
        End If

        Job("Ready")


    End Sub



    Private Function TryConnection(ByVal connstring As String) As Boolean

        Dim SuccessConnect As Boolean
        Dim conn As SqlConnection

        SuccessConnect = False

        conn = New SqlConnection()
        conn.ConnectionString = connstring
        Try
            conn.Open()
            StoreConnect = conn.ConnectionString
            SuccessConnect = True
            conn.Close()
        Catch myerror As SqlException
            SuccessConnect = False
        Finally
            conn.Dispose()
        End Try

        TryConnection = SuccessConnect

    End Function

    Public Sub ConnectionTest()
        DatabaseName = "TheStore"

        Dim TheStorePassword = "Pukka462854CDog"

        If IsTest Then

            'Test Environment
            'Data Source=warsql903;Initial Catalog=TheStore;Integrated Security=True
            If TryConnection("Data Source=LONYPLASQL01;Initial Catalog=TheStore_Test01; User Id=TheStoreUser; Password=Pukka462854CDog;") = True Then
                ServerName = "LONYPLASQL01"
                StoreConnect = StoreConnect & "; Password=" & TheStorePassword & ";"
                IsDev = False
                DatabaseName = "TheStore_Test01"
                Exit Sub
            Else
                MessageBox.Show("Could not connect to test environment", "Error connecting", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Application.Exit()

            End If

        End If

        'If InStr(IO.Directory.GetCurrentDirectory, "bin") <> 0 Then
        '    'Development Environment
        '    'Data Source=warsql903;Initial Catalog=TheStore;Integrated Security=True
        '    If TryConnection("Data Source=LONYPLASQL01;Initial Catalog=TheStore_Dev1; User Id=TheStoreUser; Password=Pukka462854CDog;") = True Then
        '        ServerName = "LONYPLASQL01"
        '        StoreConnect = StoreConnect & "; Password=" & TheStorePassword & ";"
        '        IsDev = True
        '        DatabaseName = "TheStore_Dev1"
        '        Exit Sub
        '    End If
        'End If

        If IsDev Then
            'Development Environment
            'Data Source=warsql903;Initial Catalog=TheStore; User Id=TheStoreUser; Password=Pukka462854CDog;
            'Data Source=warsql903;Initial Catalog=TheStore;Integrated Security=True
            If TryConnection("Data Source=LONYPLASQL01;Initial Catalog=TheStore_Dev1; User Id=TheStoreUser; Password=Pukka462854CDog;") = True Then
                ServerName = "LONYPLASQL01"
                StoreConnect = StoreConnect & "; Password=" & TheStorePassword & ";"
                DatabaseName = "TheStore_Dev1"
                Exit Sub
            Else
                HideSplashScreen()

                MessageBox.Show("Could not connect to development environment.  Will now try to connect to the live environment", "Development environment unavailable", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                ShowSplashScreen()

            End If
        End If

        If IsDfE Then
            'DFE
            If TryConnection("Data Source=3DCSEC-PPDB13\EFASQLPP_STORE;Initial Catalog=TheStoreLegacy; User Id=TheStoreUser; Password=Pukka462854CDog;") = True Then
                ServerName = "3DCSEC-PPDB13\EFASQLPP_STORE"
                StoreConnect = StoreConnect & "; Password=" & TheStorePassword & ";"
                DatabaseName = "TheStoreLegacy"
                Exit Sub
            End If
        End If

        ' LSC(Offices(TOLSQL905))
        'Data Source=tolsql905;Initial Catalog=TheStore;Integrated Security=True
        'Data Source=tolsql905; Initial Catalog=TheStore; User Id=TheStoreUser; Password=" & TheStorePassword & ";
        If TryConnection("Data Source=TOLSQL905;Initial Catalog=TheStore; User Id=TheStoreUser; Password=Pukka462854CDog;") = True Then
            ServerName = "tolsql905"
            StoreConnect = StoreConnect & "; Password=" & TheStorePassword & ";"
            Exit Sub
        End If



        '' YPLA RIAS Laptop
        If TryConnection("Data Source=RIAS1\MSSMLBIZ;Initial Catalog=TheStore;Integrated Security=True") = True Then
            ServerName = "RIAS1\MSSMLBIZ"
            Exit Sub
        End If


        'Add more connections here as needed

        'No succesful connection
        HideSplashScreen()
        HideLoadingForm()


        MainForm.LogonLabel.Text = "Error connecting to the database"
        MessageBox.Show(MainForm, "There was an error connecting to the database", "Error connecting to database", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Application.Exit()

    End Sub

    Public Function LoadDataViewCtrl(ByVal SqlString As String, ByVal ConnectionString As String, ByRef succeed As Boolean) As DataView

        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet

        connection = New SqlConnection(ConnectionString)

        Try
            connection.Open()

            command = New SqlCommand(SqlString, connection)

            adapter.SelectCommand = command
            adapter.Fill(ds, "Create DataView")
            adapter.Dispose()
            command.Dispose()
            connection.Close()
            succeed = True
            Return ds.Tables(0).DefaultView
        Catch ex As Exception
            succeed = False
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Nothing
        End Try

    End Function

    Public Function LoadDataView(ByVal sqlString As String) As DataView

        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet

        connection = New SqlConnection(StoreConnect)


        Try
            connection.Open()

            command = New SqlCommand(sqlString, connection)

            command.CommandTimeout = 3000

            adapter.SelectCommand = command
            adapter.Fill(ds, "Create DataView")
            adapter.Dispose()
            command.Dispose()
            connection.Close()

            LoadDataView = ds.Tables(0).DefaultView

        Catch ex As Exception
            MessageBox.Show(MainForm, ex.ToString, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            LoadDataView = Nothing
        End Try

    End Function


    Public Function LoadDataSet(ByVal sqlString As String) As DataSet

        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet

        connection = New SqlConnection(StoreConnect)


        Try
            connection.Open()

            command = New SqlCommand(sqlString, connection)

            command.CommandTimeout = 3000

            adapter.SelectCommand = command
            adapter.Fill(ds, "Create DataView")
            adapter.Dispose()
            command.Dispose()
            connection.Close()

            LoadDataSet = ds

        Catch ex As Exception
            MessageBox.Show(MainForm, ex.ToString, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            LoadDataSet = Nothing
        End Try

    End Function

    Public Function GetRID(ByVal UPIN As Integer) As String

        Dim sql As String

        sql = "select RID from relationships where UPIN = " & UPIN & " and Lead = 1"

        GetRID = LoadDataView(sql)(0).Item("RID").ToString

    End Function

    Public Function LoadDataTable(ByVal sqlString As String) As DataTable

        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataTable

        connection = New SqlConnection(StoreConnect)
        Try
            ds.Locale = System.Globalization.CultureInfo.InvariantCulture
            connection.Open()
            command = New SqlCommand(sqlString, connection)
            command.CommandTimeout = 3000

            AddHandler adapter.FillError, New FillErrorEventHandler(AddressOf FillError)


            adapter.SelectCommand = command

            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            connection.Close()

            LoadDataTable = ds

        Catch ex As Exception
            MessageBox.Show(MainForm, ex.ToString, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            LoadDataTable = Nothing
        End Try

    End Function


    Private Sub FillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)
        If args.Errors.GetType() Is Type.GetType("System.OverflowException") Then
            ' Code to handle precision loss.

            MessageBox.Show("There was an error with " & args.Values(2).ToString & ".  One of its values is too large.  This row has been ignored", "Value too large", MessageBoxButtons.OK, MessageBoxIcon.Error)

            args.Continue = True
        End If
    End Sub


    Public Function RunNonQueryParams(ByVal sql As String, ByVal Parameters As List(Of SqlParameter)) As String

        Dim cnn As SqlConnection
        Dim cmd As SqlCommand


        cnn = New SqlConnection(StoreConnect)

        Try
            cnn.Open()

            cmd = New SqlCommand(sql, cnn)

            Dim p As SqlParameter

            For Each p In Parameters
                cmd.Parameters.Add(p)
            Next

            cmd.CommandTimeout = 3000
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            cnn.Close()
            Return ""
        Catch ex As Exception

            Return ex.Message

        End Try

    End Function

    Public Function RunNonQuery(ByVal sql As String) As String
        Dim cnn As SqlConnection
        Dim cmd As SqlCommand


        cnn = New SqlConnection(StoreConnect)

        Try
            cnn.Open()

            'Change the role to have permission to write to DB
            'cmdDB1 = cnn.CreateCommand()

            ' Activate the Application role
            'cmdDB1.CommandText = "sp_setapprole"
            'cmdDB1.CommandType = CommandType.StoredProcedure
            'cmdDB1.Parameters.Clear()
            'Dim parameter As SqlParameter = cmdDB1.Parameters.Add("retvalue", SqlDbType.Int)
            'parameter.Direction = ParameterDirection.ReturnValue
            'parameter = cmdDB1.Parameters.Add("@rolename", SqlDbType.NVarChar, 128)
            'parameter.Value = "TheStoreAppRole"
            'parameter = cmdDB1.Parameters.Add("@password", SqlDbType.NVarChar, 128)
            'parameter.Value = "Pukka462854CDog"
            'parameter = cmdDB1.Parameters.Add("@fCreateCookie", SqlDbType.Bit)
            'parameter.Value = True
            'parameter = cmdDB1.Parameters.Add("@cookie", SqlDbType.VarBinary, 50)
            'parameter.Direction = ParameterDirection.Output

            'Dim myCookie As SqlTypes.SqlBinary

            'Executes the command - set the app role
            'cmdDB1.ExecuteNonQuery()

            'store the cookie
            'myCookie = cmdDB1.Parameters("@cookie").SqlValue

            'Now execute the query command
            cmd = New SqlCommand(sql, cnn)
            cmd.CommandTimeout = 3000
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            'unset the app role
            'cmdDB1.Parameters.Clear()
            'cmdDB1.CommandText = "sp_unsetapprole"
            'cmdDB1.CommandType = CommandType.StoredProcedure
            'parameter = cmdDB1.Parameters.Add("retvalue", SqlDbType.Int)
            'parameter.Direction = ParameterDirection.ReturnValue
            'parameter = cmdDB1.Parameters.Add("@cookie", SqlDbType.VarBinary, 50)
            'parameter.SqlValue = myCookie

            'Executes the command - unset the app role
            'cmdDB1.ExecuteNonQuery()

            cnn.Close()
            RunNonQuery = ""
        Catch ex As Exception
            RunNonQuery = ex.Message

            '  Clipboard.Clear()

        End Try
    End Function

    Declare Function GetUserName Lib "advapi32.dll" Alias _
       "GetUserNameA" (ByVal lpBuffer As String, _
       ByRef nSize As Integer) As Integer

    Public Function GetUserName() As String
        Dim iReturn As Integer
        Dim userName As String
        userName = New String(CChar(" "), 50)
        iReturn = GetUserName(userName, 50)
        CurrentUser.Name = userName.Substring(0, userName.IndexOf(Chr(0)))
        GetUserName = CurrentUser.Name
    End Function

    Public Sub User_Access()
        Dim TheView As DataView
        Dim rec As DataRowView
        Dim SQL As String

        SQL = "select * from User_Access where User_Name = '" & CurrentUser.Name & "'"
        TheView = LoadDataView(SQL)

        CurrentUser.Screens.Clear()

        For Each rec In TheView
            CurrentUser.Screens.Add(rec.Item("Screen_Name").ToString)
        Next

    End Sub

    Public Function ImportVolumesFromExcel(ByVal TheFileName As String, ByVal TheDataGrid As DataGridView, ByVal RID As String, ByVal PeriodName As String) As Boolean
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim r, x, fc, lc, t As Integer
        Dim xlFieldCode, FieldCode As Integer
        Dim ErrorCount As Integer
        Dim TheValue As Integer
        Dim TestValue As String
        Dim IsOriginal As Boolean
        Dim Sql As String
        Dim FieldCodes As DataView

        ErrorCount = 0

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False

        Try
            xlWorkBook = xlApp.Workbooks.Open(TheFileName)
            xlWorkSheet = xlWorkBook.Worksheets("Front Page")

            'check to see if this is the correct file to import by looking at the RID
            IsOriginal = True

            If RID <> xlWorkSheet.Cells(2, 3).value Then
                IsOriginal = False
                HideLoadingForm()
                If MessageBox.Show("Could not discover if this file is for this provider, would you like to continue importing anyway?", "RID missing", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.No Then
                    ShowLoadingForm()
                    ImportVolumesFromExcel = False
                    xlApp.Quit()
                    releaseObject(xlApp)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkSheet)
                    Exit Function
                End If
                ShowLoadingForm()
                'unprotect sheet if need be
                Try
                    xlWorkSheet.Unprotect("Pukka101")
                Catch ex As Exception

                End Try
            End If

            'Find the fieldcode and learner column
            fc = 0
            lc = 0
            For r = 1 To MaxRows
                For x = 1 To 10
                    TestValue = xlWorkSheet.Cells(r, x).value
                    If IsOriginal = False Then
                        If TestValue = "Reference for business and technical definitions" Then
                            fc = x
                        End If
                    Else
                        If TestValue = "Field_Code" Then
                            fc = x
                        End If
                    End If

                    If TestValue = "Learners" Then
                        lc = x
                    End If
                Next
            Next

            If fc = 0 Then
                ImportVolumesFromExcel = False
                Exit Function
            End If

            If lc = 0 Then
                ImportVolumesFromExcel = False
                Exit Function
            End If

            If IsOriginal = False Then
                'replace the values in the sheet with the real codes
                Sql = "select (select count(order_field) " & _
                      "from dbo.Fields_List_Part as x where x.order_field <= y.order_field and x.Field_Code <> 1000) as Sequence, Field_Code " & _
                      "from dbo.Fields_List_Part as y order by order_field"

                FieldCodes = LoadDataView(Sql)

                For t = 0 To FieldCodes.Count - 1
                    If FieldCodes(t).Item("Sequence") <> 0 Then
                        For r = 1 To MaxRows
                            Try
                                xlFieldCode = xlWorkSheet.Cells(r, fc).value
                            Catch ex As Exception
                                xlFieldCode = 0
                            End Try
                            FieldCode = FieldCodes(t).Item("Sequence")
                            If xlFieldCode = FieldCode Then
                                xlWorkSheet.Cells(r, fc).value = FieldCodes(t).Item("Field_Code")
                            End If
                        Next
                    End If
                Next

            End If


            'read down the rows into the datagridview
            For r = 1 To MaxRows
                Try
                    xlFieldCode = xlWorkSheet.Cells(r, fc).value
                Catch ex As Exception
                    xlFieldCode = 0
                End Try

                If xlFieldCode <> 0 Then
                    For x = 0 To TheDataGrid.RowCount - 1
                        FieldCode = TheDataGrid.Item(TheDataGrid.Columns("Field_Code").Index, x).Value
                        If FieldCode = xlFieldCode Then
                            'First make sure that this field is not locked
                            If TheDataGrid.Item(TheDataGrid.Columns("Locked").Index, x).Value = 0 Then
                                'write it to the grid
                                Try
                                    If xlWorkSheet.Cells(r, lc).ToString.Length <> 0 Then
                                        TheValue = xlWorkSheet.Cells(r, lc).value
                                        TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Learners").Index, x).Value = TheValue
                                    End If

                                    If xlWorkSheet.Cells(r, lc + 1).ToString.Length <> 0 Then
                                        TheValue = xlWorkSheet.Cells(r, lc + 1).value
                                        TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Achieve").Index, x).Value = TheValue
                                    End If
                                Catch ex As Exception
                                    ErrorCount = ErrorCount + 1
                                End Try
                            Else
                                If xlWorkSheet.Cells(r, lc + 1).ToString.Length <> 0 Then
                                    TheValue = xlWorkSheet.Cells(r, lc + 1).value
                                    TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Achieve").Index, x).Value = TheValue
                                End If
                            End If
                        End If
                    Next
                End If
            Next
            'xlWorkBook.SaveAs(TheFileName)
            xlWorkBook.Close()
            xlApp.Quit()
        Catch ex As Exception
            xlApp.Quit()
            ImportVolumesFromExcel = False
            Exit Function
        End Try
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        If ErrorCount = 0 Then
            ImportVolumesFromExcel = True
        Else
            ImportVolumesFromExcel = False
        End If

    End Function

    Public Function ExportVolumesToExcel(ByVal TheFileName As String, ByVal TheDataGrid As DataGridView, ByVal ProviderName As String, ByVal LocalAuthority As String, ByVal RID As String, ByVal PeriodName As String) As Boolean
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        'Dim fso As New Scripting.FileSystemObject
        Dim r, x, fc, lc As Integer
        Dim FieldCode As Integer
        Dim xlFieldCode As Integer
        Dim TestValue As String

        If Not (File.Exists(LocationofxlTemplate)) Then
            ExportVolumesToExcel = False
            Exit Function
        End If

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        Try

            xlWorkBook = xlApp.Workbooks.Open(LocationofxlTemplate)
            xlWorkBook.SaveAs(TheFileName)
            xlWorkSheet = xlWorkBook.Worksheets("Front Page")



            'write to the xl file

            xlWorkSheet.Cells(1, 1) = ProviderName
            xlWorkSheet.Cells(2, 1) = LocalAuthority
            xlWorkSheet.Cells(2, 3) = RID


            fc = 0
            lc = 0
            For r = 1 To MaxRows
                For x = 1 To 10
                    TestValue = xlWorkSheet.Cells(r, x).value
                    If TestValue = "Field_Code" Then
                        fc = x
                    End If

                    If TestValue = "Learners" Then
                        lc = x
                    End If

                Next
            Next

            If fc = 0 Then
                ExportVolumesToExcel = False
                Exit Function
            End If

            If lc = 0 Then
                ExportVolumesToExcel = False
                Exit Function
            End If


            For r = 0 To TheDataGrid.RowCount - 1
                FieldCode = TheDataGrid.Item(TheDataGrid.Columns("Field_Code").Index, r).Value
                For x = 7 To MaxRows
                    Try
                        xlFieldCode = xlWorkSheet.Cells(x, fc).value
                    Catch ex As Exception
                        xlFieldCode = 0
                    End Try
                    If xlFieldCode = FieldCode Then
                        xlWorkSheet.Cells(x, lc) = TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Learners").Index, r).Value
                        xlWorkSheet.Cells(x, lc + 1) = TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Achieve").Index, r).Value
                        xlWorkSheet.Cells(x, lc - 1) = TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Learners").Index - 1, r).Value
                        xlWorkSheet.Cells(x, lc - 2) = TheDataGrid.Item(TheDataGrid.Columns(PeriodName & " Learners").Index - 2, r).Value
                    End If
                Next
            Next
            xlWorkBook.SaveAs(TheFileName)
            xlWorkBook.Close()
            xlApp.Quit()
        Catch ex As Exception
            ExportVolumesToExcel = False
            xlApp.Quit()
            Exit Function

        End Try

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        ExportVolumesToExcel = True

    End Function

    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Public Function LA_RegionName(ByVal LA_Code As String) As String
        Dim TheView As DataView

        TheView = LoadDataView("select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code and la.LA_Code = '" & LA_Code & "'")

        If TheView.Count = 0 Then
            Return ""
        Else
            Return TheView(0).Item("Region_Name")
        End If
    End Function

    Public Function LA_Name(ByVal LA_Code As String) As String
        Dim TheView As DataView

        TheView = LoadDataView("select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code and la.LA_Code = '" & LA_Code & "'")

        If TheView.Count = 0 Then
            Return ""
        Else
            Return TheView(0).Item("LA_Name")
        End If
    End Function

    Public Function LA_Code_From_UPIN(ByVal UPIN As Integer) As String
        Dim TheView As DataView

        TheView = LoadDataView("select top 1 LA_Code from all_provider_fields where upin = " & UPIN & "")

        If TheView.Count = 0 Then
            Return ""
        Else
            Return TheView(0).Item("LA_Code")
        End If
    End Function


    Public Function Period_Name(ByVal Period_ID As Integer) As String
        Dim TheView As DataView
        Dim SQL As String
        Dim rec As DataRowView
        Dim Name As String = ""

        'check to see if the list is empty
        If Period_IDs.Count = 0 Then
            'lets load it
            SQL = "select * from Periods order by Start_Date, End_Date"
            TheView = LoadDataView(SQL)

            For Each rec In TheView
                Dim itm As New PeriodClass
                itm.Period_ID = rec.Item("Period_ID")
                itm.Period_Name = rec.Item("Period_Name")

                Period_IDs.Add(itm)
            Next

        End If

        Dim r As Integer

        For r = 0 To Period_IDs.Count - 1
            If Period_IDs(r).Period_ID = Period_ID Then
                Name = Period_IDs(r).Period_Name
            End If
        Next

        Return Name

    End Function

    Public Function GetPeriodByNum(ByVal BasePeriod_ID As Integer, ByVal Steps As Integer) As Integer
        Dim TheView As DataView
        Dim SQL As String
        Dim rec As DataRowView

        'check to see if the list is empty
        If Period_IDs.Count = 0 Then
            'lets load it
            SQL = "select * from Periods order by Start_Date, End_Date"
            TheView = LoadDataView(SQL)

            For Each rec In TheView
                Dim itm As New PeriodClass
                itm.Period_ID = rec.Item("Period_ID")
                itm.Period_Name = rec.Item("Period_Name")

                Period_IDs.Add(itm)
            Next

        End If

        'check to see if the step is 0, if so just return the baseperiod_id and exit the function
        If Steps = 0 Then
            Return BasePeriod_ID
            Exit Function
        End If

        'find the base period
        Dim r As Integer
        Dim Num As Integer = 0

        For r = 0 To Period_IDs.Count - 1
            If Period_IDs(r).Period_ID = BasePeriod_ID Then
                Num = r
            End If
        Next
        Num = Num + Steps

        If Num > Period_IDs.Count - 1 Then
            Return 0
            Exit Function
        End If

        If Num < 0 Then
            Return 0
            Exit Function
        End If

        Return Period_IDs(Num).Period_ID
    End Function

    '------------Drop Zone Functions-----------------------------
    Public Function DZ_ProviderType(ByVal UPIN As String) As String
        Dim sql As String
        Dim TheView As DataView
        Dim DZType As String

        sql = "select Provider_Type_Code from Providers where UPIN = " & UPIN
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            DZType = ""
        Else
            DZType = TheView(0).Item("Provider_Type_Code").ToString
        End If

        Select Case DZType
            Case ""
                DZ_ProviderType = "fei"
            Case "Acade"
                DZ_ProviderType = "aca"
            Case "Blank"
                DZ_ProviderType = "fei"
            Case "Schoo"
                DZ_ProviderType = "s6f"
            Case Else
                DZ_ProviderType = "fei"
        End Select

    End Function

    Public Function NextVersion(ByVal ReportID As String, ByVal RID As String) As Integer
        Dim sql As String
        Dim TheView As DataView
        Dim NV As Integer

        sql = "select Max(Version) as Version from DZ_Versions where Report_ID = '" & ReportID & "' and RID = '" & RID & "'"
        TheView = LoadDataView(sql)

        If IsDBNull(TheView(0).Item("Version")) Then
            NV = 1
        Else
            NV = TheView(0).Item("Version") + 1
        End If

        sql = "insert into DZ_Versions Values ('" & ReportID & "','" & RID & "'," & NV & ")"
        RunNonQuery(sql)

        NextVersion = NV
    End Function

    Public Function LAUPIN(ByVal LA_Code As String) As Integer
        Dim sql As String
        Dim TheView As DataView

        sql = "Select UPIN from all_provider_fields where Provider_Type_Code = 'Local' and LA_Code = '" & LA_Code & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            LAUPIN = 0
        Else
            LAUPIN = TheView(0).Item("UPIN")
        End If

        TheView = Nothing

    End Function

    Public Function LARID(ByVal LA_Code As String) As String
        Dim sql As String
        Dim TheView As DataView

        sql = "Select RID from all_provider_fields where Provider_Type_Code = 'Local' and LA_Code = '" & LA_Code & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            LARID = 0
        Else
            LARID = TheView(0).Item("RID")
        End If

        TheView = Nothing

    End Function

    Public Function LoadPeriodBox(ByRef TheBox As ComboBox) As Boolean

        Dim sql As String

        Try
            sql = "select * from periods"

            TheBox.DataSource = LoadDataView(sql)
            TheBox.ValueMember = "Period_ID"
            TheBox.DisplayMember = "Period_Name"
            Return True

        Catch ex As Exception
            Return False

        End Try

    End Function

    Public Function LoadProviderBox(ByRef TheBox As ComboBox, ByVal Provider_Type_Code As String) As Boolean

        Dim sql As String

        Dim PT As String = ""

        If Provider_Type_Code <> "" Then
            PT = " where Provider_Type_Code = '" & Provider_Type_Code & "'"
        End If

        Try
            sql = "select * from Providers" & PT

            sql = sql & " order by Provider_Name "

            TheBox.DataSource = LoadDataView(sql)
            TheBox.ValueMember = "UPIN"
            TheBox.DisplayMember = "Provider_Name"
            Return True

        Catch ex As Exception
            Return False

        End Try

    End Function


    Public Function LoadComboBox(ByRef TheBox As ComboBox, ByVal sql As String, ByVal ValueMember As String, ByVal DisplayMember As String) As Boolean

        Try
            TheBox.DataSource = LoadDataView(sql)
            TheBox.ValueMember = ValueMember
            TheBox.DisplayMember = DisplayMember
            Return True

        Catch ex As Exception
            Return False

        End Try

    End Function

    Public Function EvaluateExpression(ByVal expression As _
    String) As Double
        Dim expr As String
        Dim is_unary As Boolean
        Dim next_unary As Boolean
        Dim parens As Integer
        Dim expr_len As Integer
        Dim ch As String
        Dim lexpr As String
        Dim rexpr As String
        Dim status As Long
        Dim best_pos As Integer
        Dim best_prec As Precedence

        ' Remove all spaces.
        expr = expression.Replace(" ", "")
        expr_len = Len(expr)
        If expr_len = 0 Then Return 0

        ' If we find + or - now, it is a unary operator.
        is_unary = True

        ' So far we have nothing.
        best_prec = Precedence.None

        ' Find the operator with the lowest precedence.
        ' Look for places where there are no open
        ' parentheses.
        For pos As Integer = 0 To expr_len - 1
            ' Examine the next character.
            ch = expr.Substring(pos, 1)

            ' Assume we will not find an operator. In
            ' that case, the next operator will not
            ' be unary.
            next_unary = False

            If ch = " " Then
                ' Just skip spaces. We keep them here
                ' to make the error messages easier to
            ElseIf ch = "(" Then
                ' Increase the open parentheses count.
                parens += 1

                ' A + or - after "(" is unary.
                next_unary = True
            ElseIf ch = ")" Then
                ' Decrease the open parentheses count.
                parens -= 1

                ' An operator after ")" is not unary.
                next_unary = False

                ' If parens < 0, too many ')'s.
                If parens < 0 Then
                    Throw New FormatException( _
                        "Too many close parentheses in '" & _
                        expression & "'")
                End If
            ElseIf parens = 0 Then
                ' See if this is an operator.
                If ch = "^" Or ch = "*" Or _
                   ch = "/" Or ch = "\" Or _
                   ch = "%" Or ch = "+" Or _
                   ch = "-" _
                Then
                    ' An operator after an operator
                    ' is unary.
                    next_unary = True

                    ' See if this operator has higher
                    ' precedence than the current one.
                    Select Case ch
                        Case "^"
                            If best_prec >= Precedence.Power _
                                Then
                                best_prec = Precedence.Power
                                best_pos = pos
                            End If

                        Case "*", "/"
                            If best_prec >= Precedence.Times _
                                Then
                                best_prec = Precedence.Times
                                best_pos = pos
                            End If

                        Case "\"
                            If best_prec >= Precedence.IntDiv _
                                Then
                                best_prec = Precedence.IntDiv
                                best_pos = pos
                            End If

                        Case "%"
                            If best_prec >= Precedence.Modulus _
                                Then
                                best_prec = Precedence.Modulus
                                best_pos = pos
                            End If

                        Case "+", "-"
                            ' Ignore unary operators
                            ' for now.
                            If (Not is_unary) And _
                                best_prec >= Precedence.Plus _
                            Then
                                best_prec = Precedence.Plus
                                best_pos = pos
                            End If
                    End Select
                End If
            End If
            is_unary = next_unary
        Next pos

        ' If the parentheses count is not zero,
        ' there's a ')' missing.
        If parens <> 0 Then
            Throw New FormatException( _
                "Missing close parenthesis in '" & _
                expression & "'")
        End If

        ' Hopefully we have the operator.
        If best_prec < Precedence.None Then
            lexpr = expr.Substring(0, best_pos)
            rexpr = expr.Substring(best_pos + 1)
            Select Case expr.Substring(best_pos, 1)
                Case "^"
                    Return _
                        EvaluateExpression(lexpr) ^ _
                        EvaluateExpression(rexpr)
                Case "*"
                    Return _
                        EvaluateExpression(lexpr) * _
                        EvaluateExpression(rexpr)
                Case "/"
                    Return _
                        EvaluateExpression(lexpr) / _
                        EvaluateExpression(rexpr)
                Case "\"
                    Return _
                        CLng(EvaluateExpression(lexpr)) \ _
                        CLng(EvaluateExpression(rexpr))
                Case "%"
                    Return _
                        EvaluateExpression(lexpr) Mod _
                        EvaluateExpression(rexpr)
                Case "+"
                    Return _
                        EvaluateExpression(lexpr) + _
                        EvaluateExpression(rexpr)
                Case "-"
                    Return _
                        EvaluateExpression(lexpr) - _
                        EvaluateExpression(rexpr)
            End Select
        End If

        ' If we do not yet have an operator, there
        ' are several possibilities:
        '
        ' 1. expr is (expr2) for some expr2.
        ' 2. expr is -expr2 or +expr2 for some expr2.
        ' 3. expr is Fun(expr2) for a function Fun.
        ' 4. expr is a primitive.
        ' 5. It's a literal like "3.14159".

        ' Look for (expr2).
        If expr.StartsWith("(") And expr.EndsWith(")") Then
            ' Remove the parentheses.
            Return EvaluateExpression(expr.Substring(1, _
                expr_len - 2))
            Exit Function
        End If

        ' Look for -expr2.
        If expr.StartsWith("-") Then
            Return -EvaluateExpression(expr.Substring(1))
        End If

        ' Look for +expr2.
        If expr.StartsWith("+") Then
            Return EvaluateExpression(expr.Substring(2))
        End If

        ' Look for Fun(expr2).
        If expr_len > 5 And expr.EndsWith(")") Then
            ' Find the first (.
            Dim paren_pos As Integer = expr.IndexOf("(")
            If paren_pos > 0 Then
                ' See what the function is.
                lexpr = expr.Substring(0, paren_pos)
                rexpr = expr.Substring(paren_pos + 1, expr_len _
                    - paren_pos - 2)
                Select Case lexpr.ToLower
                    Case "sin"
                        Return Math.Sin(EvaluateExpression(rexpr))
                    Case "cos"
                        Return Math.Cos(EvaluateExpression(rexpr))
                    Case "tan"
                        Return Math.Tan(EvaluateExpression(rexpr))
                    Case "sqrt"
                        Return Math.Sqrt(EvaluateExpression(rexpr))
                    Case "factorial"
                        Return Factorial(EvaluateExpression(rexpr))
                    Case "rnd"
                        Return Math.Round((EvaluateExpression(rexpr)), 0)
                    Case "rndtwo"
                        Return Math.Round((EvaluateExpression(rexpr)), 2)
                    Case "rndthree"
                        Return Math.Round((EvaluateExpression(rexpr)), 3)
                        ' Add other functions (including
                        ' program-defined functions) here.
                End Select
            End If
        End If

        ' See if it's a primitive.
        If m_Primatives.Contains(expr) Then
            ' Return the corresponding value,
            ' converted into a Double.
            Try
                ' Try to convert the expression into a value.
                Dim value As Double = _
                    Double.Parse(m_Primatives.Item(expr).ToString)
                Return value
            Catch ex As Exception
                Throw New FormatException( _
                    "Primative '" & expr & _
                    "' has value '" & _
                    m_Primatives.Item(expr).ToString & _
                    "' which is not a Double.")
            End Try
        End If

        ' It must be a literal like "2.71828".
        Try
            ' Try to convert the expression into a Double.
            Dim value As Double = Double.Parse(expr)
            Return value
        Catch ex As Exception
            'Throw New FormatException( _
            '   "Error evaluating '" & expression & _
            '   "' as a constant.")
            Return 0
        End Try
    End Function

    ' Return the factorial of the expression.
    Private Function Factorial(ByVal value As Double) As Double
        Dim result As Double

        ' Make sure the value is an integer.
        If CLng(value) <> value Then
            Throw New ArgumentException( _
                "Parameter to Factorial function must be an " & _
                    "integer in Factorial(" & _
                Format$(value) & ")")
        End If

        result = 1
        Do While value > 1
            result = result * value
            value = value - 1
        Loop
        Return result
    End Function

    Public Function UserFormCheck(ByVal formid As String) As String

        'Function returns N (No Access), R (Read Only) or W (Write Access)

        If CurrentUser.Level <> 5 Then
            Dim SQL As String
            Dim TheView As DataView
            Dim username As String = CurrentUser.Name

            SQL = "declare @username as nvarchar(50) declare @formid as nvarchar(50) " & _
                    "set @username = '" & username & "' " & _
                    "set @formid = '" & formid & "' " & _
                    "select max(Access_Level) as Access_Level " & _
                    "from ( " & _
                    "Select gfa.Access_Level " & _
                    "from  dbo.Group_Form_Access gfa " & _
                    "inner join	dbo.Users_Groups ug on gfa.Group_ID = ug.Group_ID " & _
                    "where ug.User_Name = @username and gfa.Form_ID = @formid " & _
                    "union " & _
                    "select f.Default_Access_Level as Access_Level " & _
                    "from dbo.Forms f " & _
                    "where f.Form_ID = @formid and @username not in " & _
                    "(select ug.User_Name from dbo.Group_Form_Access gfa inner join dbo.Users_Groups ug on gfa.Group_ID = ug.Group_ID where Form_ID = @formid) " & _
                    ") as a"

            TheView = LoadDataView(SQL)

            UserFormCheck = TheView.Item(0).Item("Access_Level").ToString

            If UserFormCheck = "" Then UserFormCheck = "N"
        Else
            UserFormCheck = "W"
        End If

    End Function

    Public Function UserProviderCheck(ByVal upin As Integer) As String

        'Function returns Y (Access allowed) N (No access allowed)

        If CurrentUser.Level <> 5 Then
            Dim SQL As String
            Dim TheView As DataView
            Dim username As String = CurrentUser.Name

            SQL = "declare @username as nvarchar(50) declare @upin as int " & _
                    "set @username = '" & username & "' " & _
                    "set @upin = " & upin & " " & _
                    "select max(b.Provider_Access) as Provider_Access " & _
                    "from ( " & _
                    "select case when ptg.Group_ID is not null then 'Y' end as Provider_Access " & _
                    "from Provider_Type_Groups ptg " & _
                    "inner join Users_Groups ug on ptg.Group_ID = ug.Group_ID " & _
                    "inner join Providers p on ptg.Provider_Type_Code = p.Provider_Type_Code " & _
                    "where ug.User_Name = @username and p.UPIN = @upin " & _
                    "union " & _
                    "select 'N' as Provider_Access " & _
                    ") as b"

            TheView = LoadDataView(SQL)

            UserProviderCheck = TheView.Item(0).Item("Provider_Access").ToString

            If UserProviderCheck = "" Then UserProviderCheck = "N"
        Else
            UserProviderCheck = "Y"
        End If

    End Function

    Public Function UserFormProviderCheck(ByVal upin As Integer, ByVal formid As String) As String

        'Function returns N (No Access), R (Read Only) or W (Write Access)
        If CurrentUser.Level <> 5 Then
            Dim SQL As String
            Dim TheView As DataView
            Dim username As String = CurrentUser.Name

            SQL = "declare @username as nvarchar(50) declare @formid as nvarchar(50) declare @upin as int " & _
                    "set @username = '" & username & "' " & _
                    "set @formid = '" & formid & "' " & _
                    "set @upin = " & upin & " " & _
                    "select max(Access_Level) as Provider_Edit from ( " & _
                    "select gfa.Access_Level " & _
                    "from dbo.Group_Form_Access gfa " & _
                    "inner join dbo.Provider_Type_Groups ptg on gfa.Group_ID = ptg.Group_ID " & _
                    "inner join dbo.Users_Groups ug on gfa.Group_ID = ug.Group_ID " & _
                    "inner join dbo.Providers p on ptg.Provider_Type_Code = p.Provider_Type_Code " & _
                    "where ug.User_Name = @username and gfa.Form_ID = @formid and p.UPIN = @upin " & _
                    "union " & _
                    "select 'N' as Access_Level ) as c"

            TheView = LoadDataView(SQL)

            UserFormProviderCheck = TheView.Item(0).Item("Provider_Edit").ToString

            If UserFormProviderCheck = "" Then UserFormProviderCheck = "N"

        Else
            UserFormProviderCheck = "W"
        End If

    End Function

    Public Function UserFormProviderTypeCheck(ByVal provtype As String, ByVal formid As String) As String

        'Function returns N (No Access), R (Read Only) or W (Write Access)

        If CurrentUser.Level <> 5 Then
            Dim SQL As String
            Dim TheView As DataView
            Dim username As String = CurrentUser.Name

            SQL = "declare @provtype as nvarchar(50) declare @username as nvarchar(50) declare @formid as nvarchar(50) " & _
                "set @provtype = '" & provtype & "' " & _
                "set @username = '" & username & "' " & _
                "set @formid = '" & formid & "' " & _
                "select max(Access_Level) as Access_Level " & _
                "from (" & _
                "select gfa.Group_ID, ptg.Provider_Type_Code, gfa.Access_Level " & _
                "from Group_Form_Access gfa " & _
                "inner join dbo.Provider_Type_Groups ptg on gfa.Group_ID = ptg.Group_ID " & _
                "inner join dbo.Users_Groups ug on gfa.Group_ID = ug.Group_ID " & _
                "inner join dbo.Provider_Types ptc on ptg.Provider_Type_Code = ptc.Provider_Type_Code " & _
                "where ug.User_Name = @username and gfa.Form_ID = @formid and ptc.Provider_Type_Description = @provtype " & _
                ") as a"

            TheView = LoadDataView(SQL)

            UserFormProviderTypeCheck = TheView.Item(0).Item("Access_Level").ToString

            If UserFormProviderTypeCheck = "" Then UserFormProviderTypeCheck = "N"
        Else
            UserFormProviderTypeCheck = "W"
        End If

    End Function

    Public Sub UpdateActivePayments(ByVal Period_ID As Integer, ByVal RID As String, ByVal Allocation_ID As String)
        'this sub is ultra important.  It needs to be run evertime a change is being made to an allocation or pattern.  This will update
        'the Active_Payments table with the new payment information that will be used to create payment files and summaries

        Dim SQL As String

        'first delete the records from the table
        SQL = "delete from Active_Payments where Period_ID = " & Period_ID & " and RID = '" & RID & "' and Allocation_ID = '" & Allocation_ID & "'"
        RunNonQuery(SQL)

        'now insert the new information
        SQL = "insert into active_payments " & _
                "select Period_ID, Allocation_ID, RID, Month, Month_Total, Learner_Numbers from Live_PaymentProfiles " & _
                "where RID = '" & RID & "' " & _
                "and Period_ID = " & Period_ID & _
                " and Allocation_ID = '" & Allocation_ID & "'"

        RunNonQuery(SQL)
    End Sub

    Public Sub UpdateActivePaymentsForAllocationID(ByVal Period_ID As Integer, ByVal Allocation_ID As String)
        'first we are going to select all The RIDs sitting in the Allocation table for this allocation id regardless if they have an active allocation

        Dim SQL As String
        Dim TheView As DataView
        Dim rec As DataRowView

        SQL = "select distinct RID from allocations where Allocation_ID = '" & Allocation_ID & "' and Period_ID = " & Period_ID
        TheView = LoadDataView(SQL)

        With ProgressBarForm
            .TheProgressBar.Minimum = 0
            .TheProgressBar.Maximum = TheView.Count
            .TheProgressBar.Step = 1
            .Show()
            .Refresh()

        End With


        For Each rec In TheView

            UpdateActivePayments(Period_ID, rec.Item("RID").ToString, Allocation_ID)
            ProgressBarForm.TheProgressBar.PerformStep()
            ProgressBarForm.Refresh()
        Next

        ProgressBarForm.Close()

    End Sub

    Function RID_To_UPIN(ByVal RID As String) As Integer

        Dim theView As DataView

        theView = LoadDataView("select UPIN from All_provider_Fields where RID = '" & RID & "'")

        If theView.Count = 0 Then
            Return 0
        Else
            Return theView(0).Item("UPIN")
        End If

    End Function

    Function RID_To_UAI(ByVal RID As String) As String
        Dim theView As DataView

        theView = LoadDataView("select UAI from All_provider_Fields where RID = '" & RID & "'")

        If theView.Count = 0 Then
            Return ""
        Else
            Return theView(0).Item("UAI")
        End If
    End Function


    Function DoSomeWork(ByVal WhatWork As String, Optional ByVal Params As List(Of Object) = Nothing, Optional ByVal CanCancel As Boolean = True) As Windows.Forms.DialogResult

        If WhatWork = "" Then Return Windows.Forms.DialogResult.OK

        BusyButFine.CancelBut.Visible = CanCancel
        BusyButFine.WhatWork = WhatWork
        BusyButFine.Params = Params

        Return BusyButFine.ShowDialog

    End Function

End Module
